Поиск слева с помощью ВПР

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

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

Чтобы заполнить информацию о товаре 30001, расположенную в самом правом столбце данных (см. скриншот выше), используйте функцию ВЫБОР, чтобы изменить порядок столбцов — «переместите» самый правый столбец с искомым значением на самую левую позицию. После этого вы сможете легко получить нужное значение с помощью стандартной формулы ВПР.

Общий синтаксис

=VLOOKUP(lookup_value,CHOOSE({1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)

Чтобы заполнить информацию о товаре 30001, скопируйте или введите приведённые ниже формулы в соответствующие ячейки и нажмите Enter, чтобы получить результаты:

Цвет (ячейка H6)
=ВПР()$H$4,ВЫБОР(){1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,ЛОЖЬ)
Размер (ячейка H7)
=ВПР()$H$4,ВЫБОР(){1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),3,ЛОЖЬ)
Цена (ячейка H8)
=ВПР()$H$4,ВЫБОР(){1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),4,ЛОЖЬ)

Примечание: вместо того чтобы вводить идентификатор товара 30001непосредственно в формулы, мы использовали ссылку на ячейку $H$4(знаки доллара добавлены для создания абсолютной ссылки), поскольку хотим, чтобы формулы были динамическими. Таким образом, информацию о других товарах можно легко получить, просто изменив идентификатор товара в ячейке H3.

Пояснение к формуле

В качестве примера возьмём следующую формулу:

=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,FALSE)

• CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9):Функция ВЫБОР изменяет порядок столбцов, перемещая столбец E на первое (самое левое) место в массиве следующего вида:
  {30001,«Белый»,«Большой»,20;30002,«Чёрный»,«Большой»,21;30003,«Синий»,«Средний»,19;30004,«Красный»,«Средний»,18;30005,«Жёлтый»,«Маленький»,20}.
  В табличной форме этот массив будет выглядеть так:
  
• ВПР()$H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),2,ЛОЖЬ) = ВПР($H$4 С помощью 30001 функция ВПР найдёт строку с точным совпадением искомого значения из ячейки H4 в новой таблице, созданной функцией ВЫБОР, и вернёт результат из 2-го столбца — это значение Белый.

Связанные функции

Связанные формулы