Поиск в таблице: VLOOKUP и XLOOKUP

В этом уроке ты узнаешь

Объяснять четыре аргумента VLOOKUP и строить поиск точного совпадения
Находить причину #N/A и других типичных ошибок
Знать преимущества XLOOKUP

В прайс-листе магазина канцтоваров сотни товаров. Кассир вводит только код, а название и цена должны появляться сами. Каждый раз искать глазами по списку долго, и легко ошибиться. Эту работу выполняет одна из самых известных функций Excel — VLOOKUP (в русской версии ВПР).

Как работает VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

где:

lookup_valueискомое значение, например код товара
table_arrayтаблица; поиск идёт в её первом столбце
col_index_numиз какого по счёту столбца таблицы вернуть результат (1, 2, 3…)
range_lookupFALSE (или 0) — точное совпадение; TRUE — приблизительное

1
Берёт значение
=VLOOKUP(E2,$A$2:$C$7,3,FALSE) сначала берёт код из E2, например P104.
2
Ищет в первом столбце
Excel ищет P104 в A2:A7 сверху вниз и останавливается на первой найденной строке.
3
Отсчитывает вправо
В этой строке переходит к 3-му столбцу таблицы: A — 1, B — 2, C — 3.
4
Возвращает результат
Возвращается цена из столбца C — 18 ₼. Если код не найден, результат — #N/A.

Интерактив

Загрузка симуляции…

Прайс-лист (в манатах) и поиск по коду.

Самые частые ошибки

1) Забыть 4-й аргумент: тогда Excel ищет приблизительно и в несортированном списке может вернуть неверный результат — всегда пиши FALSE. 2) Не закрепить таблицу знаком $: при копировании формулы вниз таблица тоже сдвигается. 3) Искомое значение не в первом столбце: VLOOKUP смотрит только вправо. 4) #N/A — значение не найдено; часто причина в лишнем пробеле или числе, сохранённом как текст. 5) Если номер столбца больше числа столбцов таблицы, будет #REF!.

Приблизительный поиск: по интервалам

Маленькая таблица вместо вложенных IF

В E2:F5 записана шкала: в столбце E — 0, 50, 75, 90 (нижние границы по возрастанию), в столбце F — 2, 3, 4, 5. Балл в B2 равен 83. Как найти оценку?

Показать решение

=VLOOKUP(B2,$E$2:$F$5,2,TRUE)
При TRUE Excel находит наибольшую границу, которая меньше или равна 83, — это 75.
2-й столбец этой строки: 4.
Условие: первый столбец обязательно отсортирован по возрастанию.

XLOOKUP: современная замена

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found])

где:

lookup_arrayстолбец, в котором ищем
return_arrayстолбец, из которого берём результат
if_not_foundтекст, если ничего не найдено (необязательно)

XLOOKUP есть в Microsoft 365, Excel 2021 и более новых версиях. Тот же поиск записывается так: =XLOOKUP(E2,A2:A7,C2:C7,"Код не найден"). Не нужно считать номер столбца, точное совпадение выбрано по умолчанию, а столбец результата может находиться даже левее столбца поиска. Тренажёр Educora поддерживает только VLOOKUP, но в настоящем Excel обязательно попробуй XLOOKUP.

Свойство	VLOOKUP	XLOOKUP
Совпадение по умолчанию	приблизительное (нужно дописать `FALSE`)	точное
Может искать влево?	нет	да
При вставке столбца	номер столбца устаревает	формула остаётся верной
Если не найдено	#N/A (нужна IFERROR)	свой текст (`if_not_found`)
Версии	все версии	Microsoft 365, Excel 2021+

Главное

VLOOKUP ищет значение в первом столбце таблицы и возвращает результат из указанного столбца.
Для точного поиска 4-й аргумент должен быть FALSE (или 0); закрепляй таблицу знаками $.
#N/A — значение не найдено; покажи понятное сообщение с помощью IFERROR.
Приблизительный поиск с TRUE работает по интервалам, но первый столбец должен быть отсортирован по возрастанию.
XLOOKUP по умолчанию ищет точно, умеет искать влево и не ломается при вставке столбцов.

Проверь себя

Вопросов: 10. Каждый правильный ответ приносит XP.

1 / 10

Где в таблице VLOOKUP ищет искомое значение?