Как сделать ВПР в Excel: пошаговая инструкция с примерами

ВПР — «вертикальный просмотр» (в английской версии Excel функция называется VLOOKUP). Она ищет заданное значение в первом столбце диапазона и возвращает значение из той же строки, но из другого столбца.

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

Приём пригодится везде, где две таблицы связаны общим значением: цена из прайса в заказ, телефон клиента по фамилии, статус заказа из выгрузки.

Понадобятся два листа. На листе «Прайс» — наименования и цены:

На листе «Заказ» — наименования, количество и пустые столбцы для цены и суммы:

Два условия, без которых ВПР не заработает. Наименования в обеих таблицах должны совпадать буква в букву: «Кружка керамическая» и «кружка керамич.» для формулы — разные товары. Искомое значение должно стоять в первом столбце диапазона, по которому идёт поиск: в прайсе это столбец A с товарами.

1. 1
   Встаньте в ячейку B2 листа «Заказ»

   Это первая строка без цены — как раз под наименованием «Кружка керамическая».

2. 2
   Введите формулу

   =ВПР(A2;Прайс!$A$2:$B$6;2;ЛОЖЬ) и нажмите Enter. В ячейке появится цена — 350.

3. 3
   Протяните формулу вниз

   Схватите угол ячейки B2 и потяните до конца заказа. Цены появятся во всех строках.

4. 4
   Посчитайте сумму

   В ячейке D2 формула =B2*C2. Протяните её вниз — заказ готов.

Результат:

Синтаксис функции: ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр). Разберём на нашей формуле =ВПР(A2;Прайс!$A$2:$B$6;2;ЛОЖЬ):

Если прайс и заказ лежат на одном листе, имя листа и восклицательный знак не нужны — достаточно A2:B6. Аргументы в русской версии Excel разделяются точкой с запятой, в английской — запятой: если Excel ругается на формулу, проверьте разделитель.

Четвёртый аргумент выбирает режим поиска — и это главная ловушка функции. ЛОЖЬ означает точное совпадение: формула находит ровно то наименование, которое вы указали. ИСТИНА — приблизительное: Excel ищет ближайшее меньшее значение, и первый столбец диапазона должен быть отсортирован по возрастанию.

Если аргумент не указать, Excel подставит ИСТИНА. Тогда при несортированном прайсе формула молча вернёт не ту цену — без ошибки и предупреждения. Для наименований, артикулов и телефонов нужен только точный поиск.

Отдельная ловушка — ВПР не ищет влево. Функция ищет только в первом столбце диапазона и возвращает значения только правее. Если цена в прайсе стоит левее наименования, формула не сработает. Варианты: перенести столбцы местами, использовать связку ИНДЕКС и ПОИСКПОЗ или перейти на ПРОСМОТРX, о ней ниже.

Чтобы вместо #Н/Д показывалось понятное сообщение, оберните формулу в ЕСЛИОШИБКА: =ЕСЛИОШИБКА(ВПР(A2;Прайс!$A$2:$B$6;2;ЛОЖЬ);"Нет в прайсе"). Только не на этапе отладки: эта функция прячет и настоящие опечатки в наименованиях, так что сначала добейтесь, чтобы формулы находили всё, что должны.

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

Тот же пример на ПРОСМОТРX: =ПРОСМОТРX(A2;Прайс!$A$2:$A$6;Прайс!$B$2:$B$6;"Нет в прайсе"). Первый аргумент — что ищем, второй — столбец, где ищем, третий — столбец, откуда берём результат, четвёртый — что показать, если значения нет.

В Excel 2016 и 2019 функции нет: файл с ПРОСМОТРX, открытый в старой версии, покажет #ИМЯ?. Если файлы ходят между компьютерами с разными версиями Office, надёжнее остаться на ВПР.

В Google Таблицах работает та же функция под именем VLOOKUP (в русском интерфейсе сработает и ВПР). Отличия два: аргументы разделяются запятыми, точное совпадение задаётся словом FALSE:

=VLOOKUP(A2;'Прайс'!A2:B6;2;FALSE)

Имена листов заключаются в одинарные кавычки. В Google Таблицах доступен и XLOOKUP, он ищет в любом направлении, как в Excel 2021 и новее.

ВПР решает одну задачу — подтянуть значение из одной таблицы в другую по совпадению. Рабочая схема: наименования совпадают буква в букву, искомое значение — в первом столбце диапазона, диапазон закреплён знаками $, четвёртый аргумент — ЛОЖЬ. #Н/Д почти всегда означает пробел в конце наименования или число, сохранённое как текст. Если у вас Excel 2021 или Microsoft 365, посмотрите на ПРОСМОТРX — она снимает ограничения ВПР.

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