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

Что такое ВПР в Excel и зачем она нужна

ВПР расшифровывается как «Вертикальный Просмотр»; английский аналог — VLOOKUP (Vertical Lookup). Функция входит в категорию «Ссылки и массивы» и просматривает данные сверху вниз по крайнему левому столбцу диапазона. Ключевое ограничение: поиск работает только в первом (левом) столбце. Парная функция — ГПР (HLOOKUP, «Горизонтальный Просмотр»): она ищет по строкам. ВПР применяют в продажах, бухгалтерии, HR и аналитике данных.

Синтаксис и аргументы формулы ВПР

Полная запись: =ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр]) — четыре аргумента, последний необязателен.

Искомое значение — первый аргумент

Ссылка на ячейку с данными для поиска — например, A2 (наименование товара). Тип ссылки — относительная (без знаков $): при автозаполнении вниз адрес меняется построчно — A2, A3, A4.

Таблица — второй аргумент

Диапазон-источник: от крайнего левого столбца до столбца с нужными данными. Ссылка обязательно абсолютная: $G$2:$H$11. Чтобы зафиксировать диапазон в Excel, выделите его и нажмите F4 (Windows) или Cmd+T (macOS). Без закрепления диапазон сместится при протягивании — результаты окажутся ошибочными.

Номер столбца — третий аргумент

Порядковый номер столбца с нужными данными считается внутри диапазона, а не по всему листу. Пример: диапазон $G$2:$H$11 из двух столбцов, цена во втором — вводим 2.

Интервальный просмотр — четвёртый аргумент

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

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

Разберём пример: таблица продаж и прайс-лист — нужно подтянуть цены. Семь шагов.

Шаг 1. Добавить столбец для результата

Создайте пустой столбец в таблице продаж и поставьте курсор в его первую ячейку — здесь появится результат формулы ВПР.

Шаг 2. Открыть построитель формул или ввести =ВПР()

Способ 1: нажмите кнопку Fx на панели формул (или Shift+F3), выберите «Ссылки и массивы» → ВПР → ОК. Способ 2: наберите =ВПР() прямо в ячейке или строке формул. В Google Таблицах построителя формул нет — только ручной ввод.

Шаг 3. Указать искомое значение

Щёлкните на первую ячейку с данными для поиска — например, A2 с наименованием товара. Это относительная ссылка: при протягивании формулы вниз она сменится автоматически.

Шаг 4. Выделить диапазон и зафиксировать клавишей F4

Перейдите в поле «Таблица», выделите диапазон-источник (при необходимости переключитесь на другой лист). Нажмите F4 (Windows) или Cmd+T (macOS) — адрес примет вид $G$2:$H$11. Без знаков $ диапазон сдвинется при протягивании, и формула вернёт неверные данные.

Шаг 5. Ввести номер столбца

Укажите порядковый номер нужного столбца внутри диапазона. Два столбца в диапазоне, цена во втором — вводите 2.

Шаг 6. Задать интервальный просмотр и нажать ОК

Для текстовых наименований — 0 (ЛОЖЬ). Для числовых диапазонов — 1 (ИСТИНА). Нажмите ОК: ячейка отобразит значение, подтянутое из таблицы-источника.

Шаг 7. Протянуть формулу на весь столбец

Потяните за правый нижний угол ячейки вниз или дважды щёлкните по нему. Искомое значение меняется построчно (A2→A3→…), зафиксированный диапазон остаётся на месте.

ВПР с данными из другого листа Excel

Синтаксис межлистовой ссылки: имя листа + восклицательный знак + диапазон, например Лист2!$A$2:$B$15. При переключении на другой лист во время выбора диапазона Excel подставляет эту нотацию автоматически. Обе таблицы должны быть в одном файле: при ссылке на закрытую книгу данные перестают обновляться. Итоговая формула: =ВПР(A2;Лист2!$A$2:$B$15;2;0). В Google Таблицах разделитель аргументов — точка с запятой, остальные правила те же.

Ошибка #Н/Д в ВПР: причины и решения

Ошибка #Н/Д появляется, когда функция ВПР не находит совпадение. Чаще всего причина — лишние пробелы или скрытые символы: ячейки выглядят одинаково, но на уровне текста различаются. Решение: СЖПРОБЕЛЫ() к обеим таблицам. При дублях ВПР возвращает только первое совпадение — удалите повторы или замените функцию на сводную таблицу. Незакреплённый диапазон (пропущен F4) при протягивании тоже сдвигается и даёт ошибку.

Сравнение двух столбцов и поиск совпадений с ВПР

Функция ВПР работает как инструмент сравнения двух списков: при совпадении возвращает данные, при отсутствии — ошибку #Н/Д. Строки без ошибки — совпадения, с ошибкой — уникальные записи.

ВПР по двум условиям через вспомогательный столбец

ВПР ищет только по одному критерию. Для поиска по двум условиям добавьте вспомогательный столбец с конкатенацией (объединением) ключей: =A2&"-"&B2. Полученное значение подставляйте как искомое в ВПР: =ВПР(C2&"-"&D2; ...). Приём позволяет найти строку, где одновременно совпадают, например, товар и год поставки.

Практические задания для отработки ВПР

Три задания закрепят навык пошагово.

Задание 1 (базовое). Подтяните цены из прайс-листа (10 позиций) в таблицу продаж с помощью функции ВПР в Excel.

Задание 2 (диагностика). Намеренно пропустите F4 при вводе диапазона, протяните формулу и убедитесь в смещении. Затем исправьте, нажав F4.

Задание 3 (листы). Перенесите данные с Лист2 на Лист1 через межлистовую ссылку Лист2!$A$2:$B$15.

Хотите отработать ВПР и другие инструменты Excel на реальных задачах? В рамках федерального проекта «Активные меры содействия занятости» нацпроекта «Кадры» можно пройти программу «Excel. Системный аналитик: моделирование данных в табличных процессорах» — 72 часа, онлайн, бесплатно. Смотрите каталог доступных программ.

Часто задаваемые вопросы

Почему ВПР пишет #Н/Д, если значения в обеих таблицах выглядят одинаково?

Ошибка #Н/Д при внешне одинаковых значениях чаще всего вызвана лишними пробелами, скрытыми символами или различием форматов ячеек (текст против числа). Примените СЖПРОБЕЛЫ() к обеим таблицам. Также убедитесь, что четвёртый аргумент задан как 0 (ЛОЖЬ): при ИСТИНА функция ищет приблизительное совпадение и вернёт #Н/Д, если значение ниже минимального в диапазоне.

Что такое интервальный просмотр в ВПР — 0 или 1, что выбрать?

Интервальный просмотр — четвёртый аргумент функции ВПР. 0 (ЛОЖЬ/FALSE) — точный поиск: для текстовых наименований, имён, артикулов. 1 (ИСТИНА/TRUE) — приблизительный поиск по числовым диапазонам: ВПР находит равное или ближайшее меньшее значение. При значении 1 крайний левый столбец диапазона обязан быть отсортирован по возрастанию, иначе результат непредсказуем.

Как с помощью ВПР найти одинаковые значения в двух столбцах?

Введите =ВПР(A2;$C$2:$C$100;1;0) в новый столбец рядом с первым списком. Если значение из столбца A есть в столбце C — ВПР вернёт его. Если нет — ошибку #Н/Д. Отфильтруйте результаты: строки без ошибки — совпадения, с ошибкой — уникальные записи. Приём работает для сравнения любых двух списков.

Работает ли ВПР в Google Таблицах?

Да, функция ВПР в Google Таблицах работает по тем же правилам, что и в Excel. Единственное отличие: нет построителя формул — аргументы вводятся вручную в ячейку. Разделитель — точка с запятой. Итоговая формула идентична: =ВПР(A2;$G$2:$H$11;2;0). Правила по абсолютным ссылкам и интервальному просмотру — те же.

Чем ВПР отличается от ГПР?

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

Почему ВПР возвращает только одно значение, если в таблице есть дубли?

Это встроенное ограничение: функция ВПР всегда возвращает первое найденное совпадение в крайнем левом столбце, остальные записи игнорируются. Чтобы обработать все совпадения, удалите дубли через «Данные → Удалить дубликаты» или замените ВПР на сводную таблицу — она корректно агрегирует повторяющиеся значения.

Как закрепить диапазон таблицы в формуле ВПР?

После выделения диапазона нажмите F4 (Windows) или Cmd+T (macOS). Знаки $ появятся вокруг координат: G2:H11 превратится в $G$2:$H$11 — это абсолютная ссылка. Она не смещается при автозаполнении. Без этого шага каждая следующая строка будет искать данные в неверном диапазоне, и формула вернёт ошибочные значения.

Можно ли использовать ВПР, если обе таблицы в разных файлах Excel?

ВПР работает надёжно, когда обе таблицы находятся в одном файле — на разных листах. При ссылке на другую книгу формула работает только пока тот файл открыт: при его закрытии данные перестают обновляться. Оптимальное решение — объединить данные в один файл и использовать межлистовую ссылку вида Лист2!$A$2:$B$15.