Поиск слева с помощью VLOOKUP
В этом руководстве мы обсудим, как использовать функцию VLOOKUP для поиска информации, указанной в левых столбцах Excel, о заданном элементе, который находится справа. Мы знаем, что функция VLOOKUP не может искать значения слева, поэтому для выполнения этой задачи нам придется воспользоваться помощью функции CHOOSE .
Как выполнить поиск слева с помощью VLOOKUP?
Чтобы заполнить информацию о продукте 30001, которая указана в крайнем правом столбце данных, как показано на скриншоте выше, вы можете использовать функцию CHOOSE для переупорядочивания таблицы – «переместить» крайний правый столбец со значением поиска в самое левое положение. Затем вы сможете использовать обычную формулу VLOOKUP для получения нужного значения.
Общий синтаксис
=VLOOKUP(lookup_value,CHOOSE({1,2,3,…,N},rangeN,range1,range2,range3,…),column_num,FALSE)
- lookup_value: Значение, которое VLOOKUP использует для определения позиции соответствующей информации. Здесь имеется в виду заданный ID продукта.
- Range1, range2, range3, rangeN: Столбцы ячеек, где указаны lookup_value и другая информация.
- column_num: Номер, указывающий, из какого столбца нужно получить данные. Обратите внимание, что поскольку крайний правый столбец теперь перемещен в самое левое положение из-за функции CHOOSE, чтобы представить 1-й столбец (столбец цвета), вы должны указать column_num как 2; чтобы представить 2-й столбец (столбец размера), вы должны указать column_num как 3…
- range_lookup FALSE: Заставляет VLOOKUP находить только точное совпадение.
Чтобы заполнить информацию о продукте 30001, скопируйте или введите формулы ниже в соответствующие ячейки и нажмите Enter для получения результатов:
Цвет (Ячейка H6)
=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)
Размер (Ячейка H7)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),3,FALSE)
Цена (Ячейка H8)
=VLOOKUP($H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),4,FALSE)
Примечание: Вместо того чтобы вводить ID продукта 30001 в формулы, мы использовали ссылку на ячейку $H$4 (мы добавили знаки доллара к ссылке, чтобы сделать её абсолютной), так как хотим, чтобы формулы были динамическими. Таким образом, мы можем легко получить информацию о других продуктах, просто изменив ID продукта в ячейке 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): Функция CHOOSE переупорядочивает столбцы, перемещая столбец E на первое место, или, другими словами, в самое левое положение в массиве, например, так:
{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20}.
В табличной форме массив будет выглядеть так: - VLOOKUP($H$4$H$4,CHOOSE({1,2,3,4},$E$5:$E$9,$B$5:$B$9,$C$5:$C$9,$D$5:$D$9),22,FALSE) = VLOOKUP($H$4$H$4,{30001,"White","Large",20;30002,"Black","Large",21;30003,"Blue","Medium",19;30004,"Red","Medium",18;30005,"Yellow","Small",20},22,FALSE): С 30001, значением поиска в ячейке h3, функция VLOOKUP найдет строку его точного совпадения в новой таблице, созданной функцией CHOOSE, и вернет результат в втором столбце, который является White.
Связанные функции
Функция VLOOKUP в Excel ищет значение, сопоставляя его с первым столбцом таблицы, и возвращает соответствующее значение из определенного столбца в той же строке.
Функция CHOOSE возвращает значение из списка аргументов значений на основе заданного индексного номера. Например, CHOOSE(3,”Apple”,”Peach”,”Orange”) возвращает Orange, так как индексный номер равен 3, а Orange — это третье значение после индексного номера в функции.
Связанные формулы
Поиск слева с помощью INDEX и MATCH
Чтобы найти информацию, указанную в левых столбцах электронной таблицы Excel, о заданном элементе, который находится справа, вы можете использовать функции INDEX и MATCH. Комбинация этих двух функций имеет преимущество поиска значений в любом столбце перед другой мощной функцией поиска Excel, VLOOKUP.
Поиск значений из другого листа или книги
Если вы знаете, как использовать функцию VLOOKUP для поиска значений на одном листе, поиск значений из другого листа или книги не составит для вас проблемы.
Поиск ближайшего совпадения с несколькими критериями
В некоторых случаях вам может потребоваться найти ближайшее или приблизительное совпадение на основе более чем одного критерия. С помощью комбинации функций INDEX, MATCH и IF вы сможете быстро выполнить эту задачу в Excel.
Лучшие инструменты для повышения производительности Office
Kutools для Excel - Помогает вам выделиться из толпы
Kutools для Excel имеет более 300 функций, гарантируя, что нужный вам инструмент находится всего в одном клике...
Office Tab - Включите работу с вкладками в Microsoft Office (включая Excel)
- Один щелчок мыши, чтобы переключаться между десятками открытых документов!
- Сократите сотни кликов мышью ежедневно, попрощайтесь с болью в руке.
- Увеличивает вашу продуктивность на 50% при просмотре и редактировании нескольких документов.
- Добавляет эффективные вкладки в Office (включая Excel), как в Chrome, Edge и Firefox.