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

Научись автоматически находить название и цену товара по коду: как работает VLOOKUP, что означает #N/A и чем лучше современная XLOOKUP.

  • Средний
  • 15 мин
  • 10 вопросов
  • Урок 10 из 27

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

  • Объяснять четыре аргумента 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_lookup
FALSE (или 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.

Интерактив

Загрузка симуляции…
Прайс-лист (в манатах) и поиск по коду.

Чтобы читать дальше, зарегистрируйся бесплатно

Остальные разделы урока, тест и заметки открываются с аккаунтом Educora. Регистрация занимает минуту, а прогресс сохраняется на всех твоих устройствах.

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

Маленькая таблица вместо вложенных 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.

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

Запомни

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

Проверь себя

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

Вопрос 1 / 10

Вопрос 1 / 10. Где в таблице VLOOKUP ищет искомое значение?