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

Поиск соответствий справа налево с помощью ВПР
Поиск значений справа налево с помощью функций ВПР и ЕСЛИ
Хотя ВПР сама по себе не может искать значения справа налево, вы можете изменить структуру данных с помощью функции ЕСЛИ, чтобы это работало.
Пожалуйста, введите следующую формулу в нужную ячейку, а затем перетащите маркер заполнения в ячейки, к которым вы хотите применить эту формулу, чтобы получить все соответствующие значения. См. скриншот:
=VLOOKUP(E2, IF({1,0}, $C$2:$C$9, $A$2:$A$9), 2, 0)
- 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 сделайте следующее:
1. Нажмите «Kutools» > «Супер ПОИСК» > «Поиск справа налево», см. скриншот:
2. В диалоговом окне «Поиск справа налево» выполните следующие действия:
- Выберите ячейки с искомыми значениями и выходными ячейками из разделов «Область размещения списка» и «Диапазон значений для поиска»;
- Затем укажите соответствующие элементы из раздела «Диапазон данных».
- Наконец, нажмите кнопку OK.
3. Теперь соответствующие записи были возвращены на основе искомых значений из правого списка, см. скриншот:
Если вы хотите заменить значение ошибки #Н/Д другим текстовым значением, просто нажмите кнопку «Опции» и отметьте опцию «Заменить не найденные значения (#N/A) указанным значением», затем введите нужный текст.
Поиск значений справа налево с помощью функций ИНДЕКС и ПОИСКПОЗ
Комбинация функций ИНДЕКС и ПОИСКПОЗ является универсальной и мощной альтернативой ВПР. Она позволяет искать значения в любом направлении (слева, справа, вверх или вниз).
Введите или скопируйте следующую формулу в пустую ячейку для вывода результата, затем перетащите маркер заполнения вниз до ячеек, куда вы хотите поместить эту формулу.
=INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0))
- 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)
- E2: Это значение, которое вы ищете.
- $C$2:$C$9: Это диапазон, где Excel будет искать значение в E2. Это массив поиска.
- $A$2:$A$9: Это диапазон, из которого Excel вернёт соответствующее значение. Это массив возврата.
Хотя ВПР — отличный инструмент для базовых поисков, она ограничена возможностью искать только справа от столбца поиска. Однако есть несколько других методов, которые вы можете использовать для возврата соответствующих значений справа налево в Excel. Используя эти методы, вы можете легко и эффективно искать и возвращать соответствующие значения, независимо от того, где расположены ваши данные на листе. Если вас интересуют дополнительные советы и хитрости по работе с Excel, наш сайт предлагает тысячи учебных материалов.
Больше связанных статей:
- Поиск значений на нескольких листах
- В Excel мы можем легко применять функцию ВПР для возврата соответствующих значений в одной таблице рабочего листа. Но задумывались ли вы о том, как выполнять поиск значений на нескольких листах? Предположим, у меня есть три рабочих листа с диапазонами данных, и теперь я хочу получить часть соответствующих значений на основе критериев из этих трёх листов.
- Использование точного и приблизительного соответствия в функции ВПР в Excel
- В Excel функция ВПР является одной из самых важных для нас, чтобы искать значение в крайнем левом столбце таблицы и возвращать значение из той же строки диапазона. Но применяли ли вы успешно функцию ВПР в Excel? В этой статье я расскажу о том, как использовать функцию ВПР в Excel.
- Поиск соответствий снизу вверх в Excel
- Обычно функция ВПР помогает вам находить данные сверху вниз, чтобы получить первое соответствующее значение из списка. Но иногда вам нужно искать снизу вверх, чтобы извлечь последнее соответствующее значение. Есть ли у вас хорошие идеи для решения этой задачи в Excel?
- Поиск и возврат всей строки соответствующего значения в Excel
- Обычно вы можете выполнять поиск и возвращать соответствующее значение из диапазона данных с помощью функции ВПР, но пробовали ли вы когда-нибудь найти и вернуть всю строку данных на основе определённых критериев, как показано на следующем скриншоте.
- Поиск и объединение нескольких соответствующих значений в Excel
- Как всем известно, функция ВПР в Excel может помочь нам найти значение и вернуть соответствующие данные из другого столбца, но обычно она может получить только первое связанное значение, если существует несколько совпадающих данных. В этой статье я расскажу о том, как выполнить поиск и объединить несколько соответствующих значений в одну ячейку или вертикальный список.
Лучшие инструменты для повышения продуктивности работы с Office
Ускорьте работу в Excel с Kutools для Excel и ощутите новую степень эффективности. Kutools для Excel предлагает более300 расширенных функций для повышения продуктивности и экономии времени. Нажмите здесь, чтобы выбрать нужную вам функцию...
Office Tab добавляет вкладки в Office и делает вашу работу намного проще
- Включите редактирование и чтение с вкладками в Word, Excel, PowerPoint, Publisher, Access, Visio и Project.
- Открывайте и создавайте несколько документов во вкладках одного окна, а не в отдельных окнах.
- Увеличьте свою продуктивность на50% и сократите сотни лишних кликов мышью каждый день!