Перейти к содержимому

Как выполнить поиск значений справа налево с помощью функции ВПР в Excel?

Author: Xiaoyang Last Modified: 2025-06-05

Функция ВПР в Excel широко используется для поиска и извлечения данных. Однако одно из её ограничений заключается в том, что она может искать значения только слева направо. Это означает, что искомое значение должно находиться в первом столбце таблицы, а возвращаемое значение — справа от столбца поиска. Но что делать, если вам нужно искать значения справа налево? В этом руководстве мы рассмотрим несколько методов для достижения этой цели.


Поиск значений справа налево с помощью функций ВПР и ЕСЛИ

Хотя ВПР сама по себе не может искать значения справа налево, вы можете изменить структуру данных с помощью функции ЕСЛИ, чтобы это работало.

Пожалуйста, введите следующую формулу в нужную ячейку, а затем перетащите маркер заполнения в ячейки, к которым вы хотите применить эту формулу, чтобы получить все соответствующие значения. См. скриншот:

=VLOOKUP(E2, IF({1,0}, $C$2:$C$9, $A$2:$A$9), 2, 0)

 vlookup values from right to left with VLOOKUP and IF functions

Объяснение этой формулы:
  • E2: Это значение, которое вы ищете. Это ключ, который Excel будет искать в указанном диапазоне.
  • ЕСЛИ({1;0}; $C$2:$C$9; $A$2:$A$9)Эта часть формулы создаёт виртуальную таблицу путём перестановки столбцов. Обычно ВПР может искать только в первом столбце таблицы и возвращать значение из столбца справа. Используя ЕСЛИ({1;0}; ...), вы говорите Excel создать новую таблицу, где столбцы меняются местами:
    ♦ Первый столбец новой таблицы — $C$2:$C$9.
    ♦ Второй столбец новой таблицы — $A$2:$A$9.
  • 2: Это указывает ВПР возвращать значение из второго столбца виртуальной таблицы, созданной функцией ЕСЛИ. В данном случае оно вернёт значение из $A$2:$A$9.
  • 0: Это указывает, что вы хотите точное совпадение. Если Excel не найдёт точного совпадения для E2 в $C$2:$C$9, он вернёт ошибку.

Поиск значений справа налево с помощью Kutools для Excel

Если вы предпочитаете более удобный подход, Kutools для Excel предлагает расширенные функции, которые упрощают сложные поиски, включая возможность легко искать справа налево.

Kutools для Excel предлагает более 300 продвинутых функций для упрощения сложных задач, повышая креативность и эффективность. Интеграция с возможностями ИИ позволяет Kutools автоматизировать задачи с высокой точностью, делая управление данными без усилий. Подробная информация о Kutools для Excel...  Бесплатная пробная версия...

После установки Kutools для Excel сделайте следующее:

1. Нажмите «Kutools» > «Супер ПОИСК» > «Поиск справа налево», см. скриншот:

 click LOOKUP from Right to Left feature of kutools

2. В диалоговом окне «Поиск справа налево» выполните следующие действия:

  • Выберите ячейки с искомыми значениями и выходными ячейками из разделов «Область размещения списка» и «Диапазон значений для поиска»;
  • Затем укажите соответствующие элементы из раздела «Диапазон данных».
  • Наконец, нажмите кнопку OK.
     specify the optons in the dialog box

3. Теперь соответствующие записи были возвращены на основе искомых значений из правого списка, см. скриншот:

 vlookup values from right to left with kutools

Совет:

Если вы хотите заменить значение ошибки #Н/Д другим текстовым значением, просто нажмите кнопку «Опции» и отметьте опцию «Заменить не найденные значения (#N/A) указанным значением», затем введите нужный текст.
 Replace #N/A error value with a specified value option


Поиск значений справа налево с помощью функций ИНДЕКС и ПОИСКПОЗ

Комбинация функций ИНДЕКС и ПОИСКПОЗ является универсальной и мощной альтернативой ВПР. Она позволяет искать значения в любом направлении (слева, справа, вверх или вниз).

Введите или скопируйте следующую формулу в пустую ячейку для вывода результата, затем перетащите маркер заполнения вниз до ячеек, куда вы хотите поместить эту формулу.

=INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0))

 vlookup values from right to left with INDEX and MATCH functions

Объяснение этой формулы:
  • E2: Это значение, которое вы ищете.
  • ПОИСКПОЗ(E2; $C$2:$C$9; 0): Функция ПОИСКПОЗ ищет значение в E2 в диапазоне $C$2:$C$9. 0 указывает, что вы хотите точное совпадение. Если оно находит значение, то возвращает относительную позицию этого значения в диапазоне.
  • ИНДЕКС($A$2:$A$9; ...): Затем функция ИНДЕКС использует позицию, возвращённую функцией ПОИСКПОЗ, чтобы найти соответствующее значение в диапазоне $A$2:$A$9.

Поиск значений справа налево с помощью функции ХВПР

Если вы используете Excel 365 или Excel 2021, функция ХВПР является современной и упрощённой альтернативой ВПР. Она может искать значения в любом направлении без необходимости использования сложных формул.

Введите или скопируйте следующую формулу в пустую ячейку для вывода результата, затем перетащите маркер заполнения вниз до ячеек, куда вы хотите поместить эту формулу.

=XLOOKUP(E2,$C$2:$C$9,$A$2:$A$9)

 vlookup values from right to left with xlookup functions

Объяснение этой формулы:
  • E2: Это значение, которое вы ищете.
  • $C$2:$C$9: Это диапазон, где Excel будет искать значение в E2. Это массив поиска.
  • $A$2:$A$9: Это диапазон, из которого Excel вернёт соответствующее значение. Это массив возврата.

Хотя ВПР — отличный инструмент для базовых поисков, она ограничена возможностью искать только справа от столбца поиска. Однако есть несколько других методов, которые вы можете использовать для возврата соответствующих значений справа налево в Excel. Используя эти методы, вы можете легко и эффективно искать и возвращать соответствующие значения, независимо от того, где расположены ваши данные на листе. Если вас интересуют дополнительные советы и хитрости по работе с Excel, наш сайт предлагает тысячи учебных материалов.


Больше связанных статей:

  • Поиск значений на нескольких листах
  • В Excel мы можем легко применять функцию ВПР для возврата соответствующих значений в одной таблице рабочего листа. Но задумывались ли вы о том, как выполнять поиск значений на нескольких листах? Предположим, у меня есть три рабочих листа с диапазонами данных, и теперь я хочу получить часть соответствующих значений на основе критериев из этих трёх листов.
  • Поиск соответствий снизу вверх в Excel
  • Обычно функция ВПР помогает вам находить данные сверху вниз, чтобы получить первое соответствующее значение из списка. Но иногда вам нужно искать снизу вверх, чтобы извлечь последнее соответствующее значение. Есть ли у вас хорошие идеи для решения этой задачи в Excel?
  • Поиск и возврат всей строки соответствующего значения в Excel
  • Обычно вы можете выполнять поиск и возвращать соответствующее значение из диапазона данных с помощью функции ВПР, но пробовали ли вы когда-нибудь найти и вернуть всю строку данных на основе определённых критериев, как показано на следующем скриншоте.

  • Поиск и объединение нескольких соответствующих значений в Excel
  • Как всем известно, функция ВПР в Excel может помочь нам найти значение и вернуть соответствующие данные из другого столбца, но обычно она может получить только первое связанное значение, если существует несколько совпадающих данных. В этой статье я расскажу о том, как выполнить поиск и объединить несколько соответствующих значений в одну ячейку или вертикальный список.

Лучшие инструменты для повышения продуктивности работы с Office

🤖 Kutools AI Aide: Совершенно новый подход к анализу данных на основе: Интеллектуальное выполнение |  Генерация кода  |  Создание пользовательских формул |  Анализ данных и построение диаграмм  |  Вызов Расширенных функций Kutools
Популярные функции: Найти, выделить или отметить дубликаты | Удалить пустые строки | Объединить столбцы или ячейки без потери данных |   Округлить без формулы ...
Супер ПОИСК: VLOOKUP по нескольким критериям | VLOOKUP с несколькими значениями | Многолистовой поиск | Распознавание нечетких соответствий ....
Расширенный раскрывающийся список: Быстро создать раскрывающийся список | Зависимый раскрывающийся список | Множественный выбор в раскрывающемся списке ....
Менеджер столбцов: Добавить определённое количество столбцов | Переместить столбцы | Переключить видимость скрытых столбцов | Сравнить диапазоны и столбцы ...
Избранные функции: Сетка фокусировки | Дизайн листа | Улучшенная строка формулы | Управление книгами и листами | Библиотека автотекста | Выбор даты | Объединить данные | Шифрование/расшифровка ячеек | Отправить письмо по списку | Супер фильтр | Специальный фильтр (фильтр ячеек с жирным/курсивом/зачёркнутым...) ...
Топ-15 наборов инструментов:12 текстовых инструментов (Добавить текст, Удалить определенные символы, ...) |50+ типов диаграмм (Диаграмма Ганта, ...) |40+ практических формул (Расчет возраста на основе даты рождения, ...) |19 инструментов вставки (Вставить QR-код, Вставить изображение по пути, ...) |12 инструментов преобразования (Преобразовать в слова, Конвертация валюты, ...) |7 инструментов для объединения и разделения (Расширенное объединение строк, Разделить ячейки, ...) | ... и многое другое

Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...


Office Tab добавляет вкладки в Office и делает вашу работу намного проще

  • Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
  • Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
  • Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!