ВПР (VLOOKUP): поиск и подтягивание данных

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

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

ВПР — это функция поиска с возвратом. Она берёт одно значение, ищет его в справочнике и возвращает что-то из той же строки. Синтаксис:

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

Разберём на конкретной ситуации. Есть таблица заказов, где в столбце B — код товара. В отдельном справочнике столбец A содержит коды, столбец B — названия, столбец C — цены. Нужно подтянуть цену к каждому заказу.

Формула в ячейке D2 таблицы заказов:

=ВПР(B2; Справочник!A2:C100; 3; 0)

Что делает каждый аргумент:

• Первый — B2: что ищем. Здесь это код товара из текущей строки заказа.
• Второй — Справочник!A2:C100: где ищем.

Это диапазон на отдельном листе «Справочник».

• Третий — 3: что возвращаем. Цена стоит в третьем столбце диапазона A:C, значит указываем 3.

Если бы нужно было название — указали бы 2.

• Четвёртый — 0: режим поиска. Об этом подробнее в следующей секции.

Правило первого столбца. ВПР не выбирает, в каком столбце искать. Она всегда ищет только в первом столбце диапазона, который указан во втором аргументе. Если в примере выше сдвинуть диапазон и написать Справочник!B2:C100, то ВПР будет искать код товара в столбце B справочника — и, если там названия, ничего не найдёт.

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

Номер столбца в третьем аргументе отсчитывается не от начала листа, а от начала диапазона. Если диапазон A2:C100, то столбец A — это 1, B — 2, C — 3. Если диапазон D2:F100, то D — 1, E — 2, F — 3.

Точное совпадение (0) и закрепление диапазона знаком $

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

Теперь про копирование. Формулу =ВПР(B2; Справочник!A2:C100; 3; 0) нужно протянуть на сотни строк заказов. При копировании вниз первый аргумент B2 правильно сдвинется на B3, B4 и так далее — это нужно. Но второй аргумент Справочник!A2:C100 тоже сдвинется: в третьей строке станет Справочник!A3:C101, в четвёртой — Справочник!A4:C102. Справочник начнёт «уплывать», и часть поисков будет выполняться по неполному диапазону.

Чтобы диапазон оставался зафиксированным при любом копировании, применяются абсолютные ссылки с помощью знака $:

=ВПР(B2; Справочник!$A$2:$C$100; 3; 0)

$A$2 фиксирует и столбец A, и строку 2. $C$100 фиксирует столбец C и строку

100. При копировании вниз или вправо этот диапазон не изменится ни на одну ячейку.

Быстрый способ поставить знаки $ в Excel и Google Sheets: выделить адрес диапазона прямо в строке формул и нажать F4. Первое нажатие сделает обе координаты абсолютными ($A$2:$C$100), последующие нажатия переключают варианты.

Если в прошлом уроке вы работали с умной таблицей и дали ей имя (например, Справочник), диапазон можно записать именем таблицы — тогда $ вовсе не нужны, потому что имя таблицы само по себе не смещается при копировании:

=ВПР(B2; Справочник; 3; 0)

Оба варианта равнозначны по результату. Какой использовать — зависит от того, оформлен ли справочник как умная таблица.

Приближённое совпадение (1): подстановка по диапазону и обязательная сортировка

Режим 1 (или ИСТИНА) работает совсем иначе, чем точное совпадение. ВПР не ищет строгое равенство — она находит наибольшее значение в первом столбце, которое не превышает искомое, и возвращает данные из той строки.

Это удобно для шкальных задач. Классический пример — таблица скидок:

Сумма заказа от	Скидка
0	0%
1000	3%
5000	7%
10000	12%

Если заказ на 3 500 рублей, нужно вернуть 3% — потому что 3 500 ≥ 1 000, но < 5 000. ВПР с аргументом 1 сделает это автоматически:

=ВПР(B2; $E$2:$F$5; 2; 1)

Она найдёт наибольшее пороговое значение, не превышающее 3 500, — это 1 000 — и вернёт скидку из соседнего столбца.

Обязательное условие: первый столбец диапазона отсортирован по возрастанию.

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

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

Поэтому перед использованием режима 1 всегда убеждайтесь, что первый столбец отсортирован по возрастанию — и защитите его от случайного переупорядочивания.

Диагностика #Н/Д и #ССЫЛКА! в формулах ВПР

ВПР возвращает два характерных вида ошибок, и по тексту ошибки сразу понятно, где искать причину.

#Н/Д — искомое значение не найдено

В режиме точного совпадения эта ошибка означает, что подходящее точное значение не найдено в первом столбце диапазона. Типичные причины:

• Опечатка в символах ключа. Регистр сам по себе причиной не является: обычный ВПР в Excel и Google Sheets не различает заглавные и строчные буквы.
• Лишний пробел: значение выглядит одинаково, но в одной ячейке после текста стоит пробел. Проверьте функцией ДЛСТР() — длина строк должна совпадать.
• Несовпадение типов данных: в таблице заказов код товара 101 — это число, а в справочнике тот же код хранится как текст «101». Для Excel они разные, и точного совпадения не будет. Исправление: привести оба поля к одному типу — либо текст, либо число.
• Диапазон начинается не с того столбца: если вы случайно взяли в диапазон столбец, где не хранятся ключи, поиск идёт не по тому полю.

Диагностика #Н/Д за два шага:

1. Проверьте, что значение в первом аргументе и значение в первом столбце диапазона совпадают по типу (оба числа или оба текст) и по содержимому (нет лишних пробелов, непечатаемых символов или других опечаток).
2. Убедитесь, что диапазон начинается именно со столбца-ключа.

#ССЫЛКА! — номер столбца выходит за границу диапазона

Эта ошибка проще по диагностике. Если во втором аргументе диапазон из двух столбцов, а в третьем аргументе стоит 3 — ВПР не может вернуть данные из несуществующего третьего столбца.

Результат: #ССЫЛКА!.

Пример сломанной формулы:

=ВПР(B2; $A$2:$B$100; 3; 0)

Диапазон A:B — два столбца. Третий столбец в нём отсутствует.

Исправление — либо расширить диапазон:

=ВПР(B2; $A$2:$C$100; 3; 0)

либо уменьшить номер столбца до числа, которое не превышает ширину диапазона:

=ВПР(B2; $A$2:$B$100; 2; 0)

Обе ошибки решаются без изменения логики формулы — только проверкой соответствия данных и параметров диапазона.