Функция ВПР: ищем по вертикали Теория

ВПР: поиск по ключу в первом столбце

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

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

Функция ВПР выполняет вертикальный поиск. В английском Excel она называется VLOOKUP (Vertical Lookup).

Синтаксис

=ВПР(искомое_значение; таблица; номер_столбца; [интервальный_просмотр])
АргументЧто этоПример
искомое_значение Ключ, который ищем в справочнике A2 (код клиента из рабочей таблицы)
таблица Диапазон справочника; ключи — в первом столбце Клиенты!$A$2:$C$100
номер_столбца Порядковый номер столбца внутри диапазона, откуда берём результат 2 (второй столбец диапазона)
интервальный_просмотр ЛОЖЬ — точное совпадение; ИСТИНА — приближённое ЛОЖЬ

Пример из жизни

На листе «Продажи» в столбце A хранятся коды клиентов. На листе «Клиенты» — справочник: столбец A — коды, столбец B — имена. Нужно подтянуть имя клиента в столбец B листа «Продажи»:

=ВПР(A2; Клиенты!$A$2:$B$100; 2; ЛОЖЬ)

Функция берёт код из A2, ищет его в первом столбце диапазона Клиенты!$A$2:$B$100, находит совпадающую строку и возвращает значение из второго столбца этой строки — имя клиента.

ЛОЖЬ или ИСТИНА в последнем аргументе

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

Значение Режим поиска Когда использовать
ЛОЖЬ (или 0) Точное совпадение; для обычных кодов без подстановочных знаков ищется заданный ключ Поиск конкретного кода, ИНН, артикула или имени
ИСТИНА (или 1) Выбирает точный ключ или наибольший ключ, не превышающий искомый; возвращает поле соответствующей строки Шкалы с нижними границами интервалов, например скидки по сумме покупки
Важно: для ИСТИНА справочник обязательно должен быть отсортирован по возрастанию в столбце ключей. Без сортировки результат может оказаться неверным. Для ЛОЖЬ сортировка не нужна. Точный поиск не различает регистр букв. В текстовом ключе * и ? работают как подстановочные знаки; для поиска самих этих символов поставьте перед ними ~. Согласуйте типы ключей и удалите лишние пробелы.

Нумерация столбцов

Номер столбца отсчитывается внутри диапазона, а не по всему листу. Если диапазон начинается с колонки B, то столбец B — это номер 1, столбец C — номер 2 и т.д.

Например, диапазон $B$2:$E$20:

  • Столбец B → номер 1 (ключи)
  • Столбец C → номер 2
  • Столбец D → номер 3
  • Столбец E → номер 4

Абсолютные ссылки на диапазон

Диапазон справочника почти всегда фиксируют знаками $: $A$2:$C$100. Без фиксации при копировании формулы вниз диапазон сместится вслед за строкой, и функция начнёт искать в неверном месте.

Что происходит, если ключ не найден

При точном поиске (ЛОЖЬ) и отсутствии совпадения ВПР возвращает ошибку #Н/Д («нет данных»). В примере ниже ЕСЛИОШИБКА заменяет ошибку сообщением, но перехватывает и другие ошибки, например неверный номер столбца. Поэтому сообщение не всегда доказывает отсутствие клиента:

=ЕСЛИОШИБКА(ВПР(A2; Клиенты!$A$2:$B$100; 2; ЛОЖЬ); "Клиент не найден")

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

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

Для получения поля левее ключа используйте ПОИСКПОЗ и ИНДЕКС. При повторяющихся ключах точная ВПР возвращает первое совпадение сверху. В примерах аргументы разделены точкой с запятой; разделитель зависит от региональных настроек Excel.