ВПР: поиск по ключу в первом столбце
В этом уроке разберём аргументы ВПР, точное и приближённое совпадение, фиксацию диапазона и обработку ошибок.
ВПР помогает найти строку в справочнике по ключу и вернуть нужное поле. Например, по коду клиента можно получить его имя без ручного копирования из справочника.
Функция ВПР выполняет вертикальный поиск. В английском 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.