Как VLOOKUP ищет в таблице

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

Представьте телефонный справочник: Вы находите имя, а затем смотрите в той же строке номер. VLOOKUP делает в электронной таблице именно это.

Буква V напоминает, что поиск выполняется по вертикали (вниз по столбцу). В следующих сценах Вы изучите четыре составляющие функции и примените её к настоящей таблице цен.

VLOOKUP принимает четыре элемента, разделённых запятыми:

• искомое значение — то, что Вы ищете
• табличный массив — диапазон ячеек с Вашими данными
• номер столбца — номер столбца, из которого нужно вернуть значение
• [поиск по диапазону] — TRUE для приблизительного совпадения, FALSE для точного

Квадратные скобки означают, что последний аргумент необязателен, но почти всегда следует задавать его явно. Вот вид формулы:

Представьте небольшую таблицу товаров в ячейках A1:C4:

• Строка 1, заголовки: Код, Название, Цена
• Строка 2: A100, Яблоко, 0.50
• Строка 3: B200, Банан, 0.30
• Строка 4: C300, Вишня, 1.20

Первый столбец (Код) — это столбец, в котором VLOOKUP будет выполнять поиск. В остальных столбцах находятся данные, которые можно вернуть. Мы найдём товар по его коду и получим его цену.

Чтобы найти цену товара с кодом B200, найдите B200 в столбце 1 и верните значение из столбца 3 (Цена):

Читается это так: найдите значение "B200" в таблице A1:C4 и, если оно найдено, верните значение из 3-го столбца, используя точное совпадение (FALSE).

Результат — 0.30. VLOOKUP находит B200 в строке 3, а затем считывает значение из третьего столбца.

Номер столбца отсчитывается от левого края диапазона таблицы, а не от столбца A листа.

В диапазоне A1:C4 столбцы пронумерованы так:

• Столбец 1 = Код (столбец поиска)
• Столбец 2 = Название
• Столбец 3 = Цена

Поэтому для возврата названия используйте индекс 2, а для цены — индекс 3. Индекс 1 просто возвращает значение, которое Вы искали.

Жёстко задавать "B200" приходится редко. Обычно нужное значение находится в другой ячейке. Представьте, что кто-то вводит код в E2. Укажите для VLOOKUP эту ячейку вместо фиксированного текста.

Теперь при каждом изменении E2 результат будет обновляться автоматически. Так поиск используется в счетах, информационных панелях и полях поиска.

У VLOOKUP есть строгое правило: он может искать только в крайнем левом столбце табличного массива. Он не может искать в столбце 2, а затем возвращаться к столбцу 1.

Поэтому столбец, в котором нужно выполнять поиск, должен быть первым столбцом выбранного диапазона. Если ваши коды находятся в столбце B, начните табличный массив со столбца B, например B1:D4.

Это ограничение поиска по левому столбцу чаще всего вызывает затруднения при работе с VLOOKUP. В одном из следующих уроков рассматривается способ обойти его.

Вы можете включить строку заголовков в табличный массив или исключить её. Оба варианта работают:

• A1:C4 включает заголовки (Код, Название, Цена)
• A2:C4 исключает заголовки

При точном совпадении (FALSE) заголовки не приводят к неверным результатам, потому что они не совпадут с кодом товара. Многие включают их, чтобы диапазон было проще читать. Только помните: номер столбца по-прежнему отсчитывается от левого края выбранного диапазона.

Представьте, что вы создаёте счёт на оплату. Код товара находится в A10, а из таблицы нужно получить его название и цену.

Название в B10:

Цена в C10:

Одна таблица заполняет множество ячеек. Достаточно ввести код один раз — и остальные данные будут заполнены. В этом и заключается практическая сила VLOOKUP.

Вы заметили знаки $ в $A$1:$C$4? Когда вы копируете VLOOKUP вниз по столбцу, нужно, чтобы искомое значение менялось (A10, A11, A12...), а таблица оставалась зафиксированной.

Абсолютные ссылки со знаками доллара фиксируют таблицу на месте. Без них при копировании таблица сместится за пределы ваших данных и появятся ошибки. Зафиксируйте табличный массив, а искомое значение оставьте относительным.

Таблица с данными часто находится на другой вкладке. Чтобы обратиться к диапазону на листе с названием Товары, укажите имя листа и восклицательный знак перед диапазоном.

Если в имени листа есть пробелы, заключите его в одинарные кавычки, например 'Price List'!A:C. Поиск работает точно так же, просто данные считываются с другой вкладки.

Проверьте, насколько хорошо вы поняли принцип поиска с помощью VLOOKUP.

Теперь вы знаете основу VLOOKUP:

• Он ищет вниз по первому столбцу табличного массива
• Он принимает четыре аргумента: искомое значение, табличный массив, номер столбца и поиск по диапазону
• Номер столбца отсчитывается от левого края диапазона
• В большинстве случаев используйте FALSE для точного совпадения
• Зафиксируйте таблицу с помощью $, чтобы при копировании вниз она оставалась на месте

Далее вы подробно разберёте разницу между точным и приближённым совпадением.