Функция ВПР в Excel: что это и как работает

ВПР в Excel — функция, которая находит значение в первом столбце таблицы и возвращает данные из той же строки в другом столбце. Например, по артикулу товара она подтягивает цену из справочника. Синтаксис: =ВПР(искомое_значение; таблица; номер_столбца; ЛОЖЬ), где ЛОЖЬ означает точное совпадение. Если значение не найдено, ВПР возвращает ошибку #Н/Д.

Синтаксис функции ВПР

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

Как работает ВПР: интерактивная схема

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

Три примера ВПР

1. Цена товара по артикулу

В таблице заказов в A2 артикул, справочник цен — на листе «Цены» в столбцах A:C, цена в третьем столбце:

=ВПР(A2;Цены!$A$2:$C$500;3;ЛОЖЬ)

2. Оклад сотрудника по табельному номеру

Табельный номер в B2, справочник сотрудников в $H$2:$K$200, оклад в четвёртом столбце справочника:

=ВПР(B2;$H$2:$K$200;4;0)

3. Скидка по сумме заказа (поиск по интервалу)

Шкала скидок: от 0 ₽ — 0%, от 50 000 ₽ — 3%, от 100 000 ₽ — 5%, от 300 000 ₽ — 7%. Пороги записаны по возрастанию в $M$2:$N$5. Сумма заказа в C2:

=ВПР(C2;$M$2:$N$5;2;ИСТИНА)

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

Почему ВПР выдаёт #Н/Д и другие ошибки

Ошибка #ССЫЛКА! означает, что номер столбца больше числа столбцов в диапазоне. Ошибка #ЗНАЧ! — что номер столбца меньше 1.

ВПР по двум условиям

ВПР ищет только по одному столбцу. Если строку определяют два признака — например, товар и месяц, — сделайте в справочнике вспомогательный первый столбец, который склеивает оба признака:

Справочник, столбец A:  =B2&"|"&C2        → «Ноутбук|Октябрь»
Поиск в отчёте:        =ВПР(F2&"|"&G2;$A$2:$D$500;4;ЛОЖЬ)

Разделитель «|» нужен, чтобы «Мышь1» + «0» не совпало с «Мышь» + «10». В Excel 2021 и Microsoft 365 то же решается без вспомогательного столбца: =ПРОСМОТРX(F2&"|"&G2;B2:B500&"|"&C2:C500;D2:D500).

ВПР с подстановочными знаками

При точном поиске (ЛОЖЬ) ВПР понимает подстановочные знаки: * — любое количество символов, ? — один символ. Это удобно, когда в справочнике полное название, а у вас только его часть:

=ВПР("*"&A2&"*";$E$2:$G$500;3;ЛОЖЬ)   → найдёт «ООО Ромашка-Трейд» по слову «Ромашка»

ВПР вернёт первую подходящую строку, поэтому убедитесь, что фрагмент однозначен.

ИНДЕКС и ПОИСКПОЗ вместо ВПР

Связка работает во всех версиях Excel и снимает главные ограничения ВПР: ищет в любом столбце, возвращает значения левее искомого и не ломается при вставке столбцов.

=ИНДЕКС($C$2:$C$500;ПОИСКПОЗ(A2;$A$2:$A$500;0))

ПОИСКПОЗ находит номер строки с искомым значением (0 — точное совпадение), а ИНДЕКС берёт ячейку с этим номером из столбца результата.

ВПР из другого листа и другого файла

Для другого листа укажите его имя перед диапазоном: =ВПР(A2;Цены!$A$2:$C$500;3;ЛОЖЬ). Для другого файла откройте оба файла и при вводе формулы выделите диапазон мышью — Excel подставит ссылку вида [Прайс.xlsx]Цены!$A$2:$C$500. Если файл-источник закрыть, Excel запомнит полный путь к нему; при переносе файлов ссылки придётся обновить через «Данные» → «Изменить связи».

Как ускорить ВПР на больших таблицах

• Ограничьте диапазон реальными строками или используйте таблицу (Ctrl+T) вместо ссылок на целые столбцы.
• Не пересчитывайте одно и то же. Если из справочника нужно несколько столбцов, найдите номер строки один раз через ПОИСКПОЗ, а затем берите значения через ИНДЕКС.
• Замените формулы значениями, когда результат больше не меняется: «Копировать» → «Вставить» → «Значения».
• Переходите на Power Query, если объединяете таблицы на десятки тысяч строк каждую неделю.

ВПР или ПРОСМОТРX

В Excel 2021 и Microsoft 365 есть ПРОСМОТРX — более гибкая замена ВПР:

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

Если файл откроют в старых версиях Excel, оставляйте ВПР или используйте связку ИНДЕКС и ПОИСКПОЗ. Для регулярного объединения больших таблиц удобнее Power Query.

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