Функция ВПР в Excel: как пользоваться, примеры и частые ошибки

ВПР нужна, когда в одной таблице есть код или название, а нужные данные лежат в другой. Например, есть список заказов с артикулами, а цены записаны в прайсе. Вместо того чтобы искать каждую цену глазами, ВПР подставит её сама. Ниже разберём функцию на примере, потом частые ошибки и что использовать вместо неё в новых версиях Excel.

Как устроена формула

Полная запись выглядит так:

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])

• Искомое значение: что ищем. Обычно ссылка на ячейку, например артикул в A2.
• Таблица: диапазон, в котором ищем. Искомое значение должно быть в его первом столбце.
• Номер столбца: из какого столбца этого диапазона вернуть результат. Первый столбец диапазона имеет номер 1.
• Интервальный просмотр: ЛОЖЬ (или 0) для точного совпадения, ИСТИНА (или 1) для приблизительного. В рабочих задачах почти всегда нужен 0.

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

Пример: подставить цены из прайса

На листе «Заказы» в столбце A артикулы, в столбце B нужно получить цену. На листе «Прайс» в столбце A артикулы, в столбце B название, в столбце C цена.

1. Встаньте в ячейку B2 на листе «Заказы».
2. Введите формулу =ВПР(A2;Прайс!$A$2:$C$500;3;0).
3. Нажмите Enter: в ячейке появится цена для артикула из A2.
4. Протяните формулу вниз на весь столбец.

Знаки $ закрепляют диапазон прайса. Без них при протягивании формулы диапазон «поедет» вниз, и в нижних строках ВПР начнёт искать не в том месте.

Частые ошибки

• #Н/Д. Значение не найдено. Проверьте, есть ли оно в первом столбце диапазона. Частые причины: лишний пробел в конце, число записано как текст в одной таблице и как число в другой.
• Не та цена. Забыли четвёртый аргумент, и Excel включил приблизительный поиск. Всегда ставьте 0 в конце.
• #ССЫЛКА! Номер столбца больше, чем столбцов в выбранном диапазоне.
• Результат сломался после вставки столбца. Номер столбца в ВПР фиксированный, Excel его не пересчитывает. Если в прайс добавили столбец, номер нужно поправить вручную.
• Нужно искать влево. ВПР ищет только в первом столбце и возвращает значения правее него. Для поиска влево нужна связка ИНДЕКС и ПОИСКПОЗ или ПРОСМОТРX.

Чтобы вместо #Н/Д показать понятный текст, оберните формулу: =ЕСЛИОШИБКА(ВПР(A2;Прайс!$A$2:$C$500;3;0);"нет в прайсе").

Чем заменить ВПР в новых версиях

В Excel для Microsoft 365 и Excel 2021 есть функция ПРОСМОТРX (XLOOKUP). Она ищет в любую сторону, не ломается при вставке столбцов и по умолчанию ищет точное совпадение:

=ПРОСМОТРX(A2;Прайс!$A$2:$A$500;Прайс!$C$2:$C$500;"нет в прайсе")

Если файл открывают коллеги со старыми версиями Excel, у них ПРОСМОТРX не сработает. Тогда надёжнее ИНДЕКС и ПОИСКПОЗ: =ИНДЕКС(Прайс!$C$2:$C$500;ПОИСКПОЗ(A2;Прайс!$A$2:$A$500;0)).

В Google Таблицах

В Google Таблицах ВПР работает так же, аргументы те же. Есть и аналог ПРОСМОТРX: функция XLOOKUP.

Где пригодится дальше

ВПР одна из первых функций, без которых не обходится работа с отчётами. Дальше обычно осваивают сводные таблицы, ЕСЛИ и СУММЕСЛИ, условное форматирование и Power Query для регулярных выгрузок. Если таблицы занимают заметную часть рабочего дня, короткий курс Excel окупается быстро: он экономит часы на ручном поиске и сверке.

Частые вопросы