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

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

Все формулы и результаты в статье я проверил в Microsoft Excel 2021 (сборка 16.0.17002.20000) с русским интерфейсом, картинки сняты прямо из него. Учебный файл со всеми примерами можно скачать и повторять шаги вместе со статьёй.

Как работает ВПР в Excel

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

Формула ВПР в Excel по аргументам

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

Аргументы в русском Excel разделяются точкой с запятой, а в английском запятой. Одна и та же формула из моего примера в двух видах:

=ВПР(A2;Прайс!$A$2:$D$9;2;ЛОЖЬ)
=VLOOKUP(A2,Прайс!$A$2:$D$9,2,FALSE)

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

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

Задача: есть прайс-лист с артикулами, названиями, ценами и остатками. Нужно сделать заказ, в котором достаточно вписать артикул и количество, а название и цена подставятся сами.

Шаг 1. Подготовьте прайс

Прайс лежит на листе «Прайс», в диапазоне A1:D9:

Главное требование ВПР: то, по чему ищем (артикул), должно стоять в первом столбце таблицы. Порядок остальных столбцов не важен, сортировать прайс для точного поиска не нужно.

Шаг 2. Напишите первую формулу

На листе «Заказ» в столбце A стоят артикулы, в столбце D количество. Встаньте в B2 и введите:

=ВПР(A2;Прайс!$A$2:$D$9;2;ЛОЖЬ)

Читается она так: возьми артикул из A2, найди его в первом столбце диапазона A2:D9 на листе «Прайс», верни значение из 2-го столбца этого диапазона (название товара), ищи только точное совпадение. Для цены в C2 формула та же, меняется только номер столбца:

=ВПР(A2;Прайс!$A$2:$D$9;3;ЛОЖЬ)

Номер столбца считается от начала выделенной таблицы, а не от столбца A листа. Если бы таблица начиналась со столбца C, номер 2 означал бы столбец D.

Набирать адрес таблицы руками не обязательно. Когда дойдёте до второго аргумента, щёлкните ярлычок листа «Прайс» и выделите мышью диапазон A2:D9: Excel сам подставит Прайс!A2:D9. Затем нажмите F4, и адрес превратится в Прайс!$A$2:$D$9.

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

Выделите B2:C2 и потяните за маркер в правом нижнем углу до последней строки заказа. Вот как это выглядит в режиме показа формул (Ctrl+Ё, клавиша слева от единицы, или вкладка «Формулы» → «Показать формулы»):

Ссылка на артикул сдвигается (A2, A3, A4…), а адрес прайса остаётся одним и тем же. Сумму считаем обычным умножением =C2*D2, итог через =СУММ(E2:E5). В Excel получилось так:

Теперь достаточно поменять артикул в столбце A, и строка пересчитается сама. Если в прайсе изменится цена, заказ тоже обновится.

ЛОЖЬ или ИСТИНА: где легко ошибиться

Четвёртый аргумент ВПР необязательный, и в этом главная ловушка. По справке Microsoft, если его не указать, Excel считает его равным ИСТИНА и ищет приблизительное совпадение. При приблизительном поиске ВПР не говорит «не нашёл», а берёт ближайшее меньшее значение.

Я проверил на артикуле A-110, которого в прайсе нет:

Вторая формула без четвёртого аргумента уверенно вернула «Наушники», последний товар в прайсе: A-110 по алфавиту больше A-108, и Excel решил, что это подходящая строка. В заказе такая ошибка незаметна, просто в счёт попадёт чужой товар по чужой цене. Поэтому правило простое: для поиска по артикулу, коду, фамилии или номеру всегда пишите ЛОЖЬ или 0. Это одно и то же: =ВПР(A2;Прайс!$A$2:$D$9;2;0) работает так же.

Когда ИСТИНА нужна

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

Сумма 7 500 ₽ попала в ступень «от 5 000», а ровно 10 000 ₽ уже в ступень «от 10 000». Обязательное условие: первый столбец шкалы отсортирован по возрастанию. Я перемешал строки той же шкалы (10 000, 0, 20 000, 5 000) и получил для 7 500 ₽ скидку 0 % вместо 3 %, а для 25 000 ₽ скидку 3 % вместо 7 %. Ошибки Excel при этом не показал.

Зачем знаки $ при протягивании

Адрес без знаков доллара относительный: при копировании вниз на одну строку Excel сдвигает его тоже на одну строку. Для ссылки на артикул это нужно, для ссылки на прайс нет. Я протянул формулу =ВПР(A2;Прайс!A2:D9;2;ЛОЖЬ) без $ на четыре строки заказа:

Таблица поиска уползла вниз, и артикулы A-101 и A-102 из верхних строк прайса в неё больше не попадают. Хуже всего, что строка 4 «случайно» работает: веб-камера стоит ниже, и ошибку легко не заметить на маленьком примере. Знак $ перед буквой закрепляет столбец, перед цифрой строку, $A$2:$D$9 закрепляет всё. Клавиша F4 при курсоре на адресе перебирает варианты.

Другой способ не думать о $: превратить прайс в «умную» таблицу (Ctrl+T) или выделить столбцы целиком: Прайс!$A:$D. Ссылка на целые столбцы не съедет, а новые строки прайса сразу попадут в поиск.

ВПР в Excel из другой таблицы

Из другого листа

В примере выше прайс уже лежит на другом листе. Адрес листа пишется перед диапазоном через восклицательный знак: Прайс!$A$2:$D$9. Если в названии листа есть пробел, Excel возьмёт его в одинарные кавычки: 'Прайс 2026'!$A$2:$D$9. Проще всего не набирать это руками, а щёлкнуть по листу и выделить диапазон мышью во время ввода формулы.

Из другой книги (файла)

Откройте оба файла, начните формулу в заказе и выделите диапазон в файле с прайсом. Я сделал это на двух файлах, prajs.xlsx и zakaz-iz-drugoy-knigi.xlsx. Пока прайс открыт, формула выглядит так:

=ВПР(A2;[prajs.xlsx]Прайс!$A$2:$D$9;3;ЛОЖЬ)

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

=ВПР(A2;'C:\Папка\[prajs.xlsx]Прайс'!$A$2:$D$9;3;ЛОЖЬ)

Формула вернула 1 490 для клавиатуры и продолжила показывать это значение и с закрытым прайсом. Дальше я поменял цену в закрытом файле прайса на 1 590 и открыл заказ без обновления связей: в ячейке осталось старое 1 490. После обновления связи появилось 1 590. Отсюда два практических вывода:

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

Если данные приходят в виде нескольких выгрузок CSV, их удобно сначала склеить в одну таблицу, как в статье Как объединить несколько CSV-файлов в один, и уже по ней искать через ВПР.

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

Я собрал типичные ошибки на одном листе и посчитал в Excel. Слева описание, справа настоящий результат и текст формулы (выведен функцией Ф.ТЕКСТ):

Лишние пробелы: СЖПРОБЕЛЫ

Артикул «A-103 » с пробелом на конце на глаз не отличить от «A-103», но для ВПР это разные строки: результат #Н/Д. Формула =ВПР(СЖПРОБЕЛЫ(B7);Прайс!$A$2:$D$9;2;ЛОЖЬ) убирает пробелы по краям и находит «Клавиатуру». Пробелы чаще всего приносят выгрузки из 1С, сайтов и CRM. Если пробелы сидят в самом прайсе, а не в искомом значении, СЖПРОБЕЛЫ в формуле не поможет: прайс нужно почистить (вспомогательный столбец с СЖПРОБЕЛЫ, затем «Вставить как значения»).

Числа как текст

В моём примере коды товаров 1001, 1002, 1003 записаны числами, а искомое «1002» хранится как текст (так бывает после копирования с сайта или из выгрузки). Итог #Н/Д. Тот же поиск с --B10 (двойной минус превращает текст в число) возвращает «Свитч», и обычное число в ячейке тоже находит «Свитч». Такие ячейки Excel обычно помечает зелёным треугольником в углу, а текстовые числа по умолчанию прижаты к левому краю.

ЕСЛИОШИБКА: вместо #Н/Д свой текст

Если отсутствие значения нормальная ситуация (например в заказ вписали товар, которого нет в прайсе), оберните формулу:

=ЕСЛИОШИБКА(ВПР(B8;Прайс!$A$2:$D$9;2;ЛОЖЬ);"нет в прайсе")

Для A-110 Excel показал «нет в прайсе». Только не оборачивайте в ЕСЛИОШИБКА всё подряд с самого начала: она прячет любые ошибки, в том числе #ССЫЛКА! от неверного номера столбца. Сначала добейтесь, чтобы формула работала, потом добавляйте обёртку. Если нужно скрыть именно «не найдено», есть функция ЕСНД, она перехватывает только #Н/Д.

Функция ЕСЛИ в Excel с ВПР

ВПР часто кладут внутрь ЕСЛИ, чтобы принять решение по найденному значению. В заказе столбец «Наличие» сравнивает остаток из прайса (4-й столбец) с заказанным количеством:

=ЕСЛИ(ВПР(A2;Прайс!$A$2:$D$9;4;ЛОЖЬ)>=D2;"в наличии";"не хватает")

Для флешки (остаток 20, заказано 3) Excel написал «в наличии», для беспроводной мыши (остаток 0, заказано 2) «не хватает». Это видно на картинке с заказом выше. По тому же принципу можно сделать =ЕСЛИ(ЕНД(ВПР(...));"новый клиент";"есть в базе"): ЕНД возвращает ИСТИНА, если ВПР ничего не нашла.

Excel: ВПР и сводные таблицы

ВПР и сводные таблицы встречаются в двух ситуациях.

ВПР перед сводной. В журнале продаж обычно есть только артикул и количество. Чтобы построить сводную по товарам и выручке, я добавил в журнал два столбца: =ВПР(B2;Прайс!$A$2:$D$9;2;ЛОЖЬ) для названия и =C2*ВПР(B2;Прайс!$A$2:$D$9;3;ЛОЖЬ) для суммы. Сводная по шести продажам показала: флешка 5 520 ₽, наушники 3 580 ₽, веб-камера 2 390 ₽, кабель HDMI 1 350 ₽, общий итог 12 840 ₽. Обычная сводная строится по одной таблице, поэтому недостающие столбцы удобно заранее подтянуть через ВПР.

ВПР по готовой сводной. Сводная на листе это обычные ячейки, и ВПР по ним работает: =ВПР(K2;$H:$I;2;ЛОЖЬ) нашла в сводной «Флешка 64 ГБ» и вернула 5 520. Ссылку лучше давать на целые столбцы, потому что после обновления сводная может вырасти или сжаться. При этом искать нужно по названию строки, а не по номеру позиции: порядок строк в сводной меняется.

Ограничения ВПР

Ищет только в первом столбце и возвращает только правее. Найти артикул по названию товара ВПР не может: название стоит во втором столбце, артикул в первом. Формула =ВПР("Веб-камера";Прайс!$A$2:$D$9;1;ЛОЖЬ) дала #Н/Д, потому что «Веб-камеру» Excel искал среди артикулов.

Возвращает первое совпадение. В маленькой таблице клиентов Иванов встречается дважды, с суммами 1 200 и 3 500 ₽. =ВПР("Иванов";$E$2:$F$4;2;ЛОЖЬ) вернула 1 200, вторую строку ВПР не видит. Для таблиц с повторами нужны СУММЕСЛИ (сложить всё) или ПРОСМОТРX с поиском с конца (взять последнее).

Номер столбца задан числом. Если вставить в прайс новый столбец между артикулом и ценой, формула продолжит брать третий столбец, где теперь будет уже не цена. Excel об этом не предупредит.

Когда лучше ИНДЕКС+ПОИСКПОЗ или ПРОСМОТРX

ИНДЕКС и ПОИСКПОЗ

Связка работает во всех версиях Excel и снимает ограничение «только вправо». ПОИСКПОЗ находит номер строки, ИНДЕКС достаёт значение из любого столбца:

=ИНДЕКС(Прайс!$A$2:$A$9;ПОИСКПОЗ(A2;Прайс!$B$2:$B$9;0))

Для «Веб-камеры» формула вернула артикул A-107. Ноль в ПОИСКПОЗ означает точный поиск, как ЛОЖЬ в ВПР. Столбцы указываются отдельными диапазонами, поэтому вставка новых столбцов в прайс формулу не ломает.

ПРОСМОТРX

Новая функция поиска, которую Microsoft называет улучшенной версией ВПР. По справке Microsoft она есть в Excel для Microsoft 365, Excel 2024 и Excel 2021, а в Excel 2016 и Excel 2019 её нет. В моём Excel 2021 она работает:

=ПРОСМОТРX(A3;Прайс!$B$2:$B$9;Прайс!$A$2:$A$9;"нет")

Аргументы: что ищем, где ищем, откуда брать результат, что вывести, если не нашли. Для «Веб-камеры» результат A-107, для «Монитора», которого нет в прайсе, текст «нет». Точный поиск включён по умолчанию, ЕСЛИОШИБКА не нужна. А шестой аргумент -1 ищет с конца: =ПРОСМОТРX("Иванов";$E$2:$E$4;$F$2:$F$4;"нет";0;-1) вернула последнюю сумму Иванова, 3 500.

Если файл откроют в Excel 2019 или старше, вместо результатов ПРОСМОТРX там будет ошибка. Для таких случаев остаётся ВПР или ИНДЕКС+ПОИСКПОЗ.

ВПР в Google Таблицах и LibreOffice Calc

Google Таблицы. Функция есть, английское название VLOOKUP, в русской справке Google записана так: ВПР(запрос; диапазон; индекс; [отсортировано]). Логика та же, что в Excel, и по умолчанию четвёртый аргумент тоже ИСТИНА, поэтому Google прямо рекомендует указывать ЛОЖЬ. Русские названия функций работают, если в настройках Google Таблиц снят флажок «Всегда использовать названия функций на английском языке». Разделитель аргументов зависит от региональных настроек таблицы (Файл → Настройки): если формула с точками с запятой не принимается, замените их на запятые. ПРОСМОТРX в Google Таблицах тоже есть, под именем XLOOKUP.

LibreOffice Calc. Функция называется ВПР, синтаксис по справке: ВПР(Критерий поиска; Массив; Индекс [; Сортированный диапазон поиска]), аргументы разделяются точкой с запятой. Четвёртый аргумент по умолчанию тоже считается ИСТИНА, так что ЛОЖЬ или 0 нужно писать и здесь. Функция XLOOKUP появилась в LibreOffice 24.8.

Файл vpr-primer.xlsx из этой статьи можно открыть и в Google Таблицах, и в LibreOffice: ВПР там есть. Сам я проверял пример только в Excel, а ПРОСМОТРX в старых версиях LibreOffice покажет ошибку.

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