ВПР в Excel: формула, пример, точное совпадение и ошибки

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

Разберём конкретную задачу: в F2 записан код клиента C-1088. В диапазоне A2:D5 находится справочник клиентов, и нужно вернуть кредитный лимит из четвёртого столбца.

В F2:

C-1088

Формула:

=ВПР(F2;$A$2:$D$5;4;ЛОЖЬ)

В английской версии Excel:

=VLOOKUP(F2,$A$2:$D$5,4,FALSE)

Результат — 700000.

Формулу удобно читать слева направо:

1. F2 — что ищем. В нашем примере это код C-1088.
2. $A$2:$D$5 — где ищем. ВПР просматривает первый столбец этого диапазона, то есть столбец A.
3. 4 — из какого столбца вернуть результат. Внутри диапазона A:D четвёртый столбец — Лимит.
4. ЛОЖЬ — требуется точное совпадение.

Для кодов, артикулов, ID, номеров договоров и других уникальных ключей почти всегда нужен точный поиск ЛОЖЬ / FALSE.

Справочник записан как $A$2:$D$5, чтобы он не сдвигался при копировании формулы вниз.

Если написать A2:D5, то после копирования на следующую строку Excel превратит диапазон в A3:D6, затем в A4:D7 и так далее. Это одна из типичных причин, когда первые строки выглядят правильными, а ниже появляются ошибки или неверные значения.

Ячейка F2, наоборот, оставлена относительной: при копировании вниз она должна стать F3, F4, F5.

Четвёртый аргумент определяет режим поиска.

ЛОЖЬ или FALSE означает: вернуть результат только тогда, когда найден именно такой ключ.

Если точного кода C-1088 в справочнике нет, Excel вернёт #Н/Д. Для рабочего отчёта это полезный сигнал: ключ отсутствует или данные подготовлены неправильно.

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

Если ВПР возвращает #Н/Д, не маскируйте ошибку сразу. Сначала найдите причину.

Проверьте:

• существует ли ключ в первом столбце справочника;
• одинаковый ли тип данных у ключей;
• нет ли лишних пробелов;
• не хранится ли число как текст;
• не перепутаны ли похожие символы;
• точно ли первый столбец выбранного диапазона содержит ключ поиска.

Например, текстовый код 00125 и число 125 визуально могут выглядеть связанными, но для Excel это разные значения.

Когда вы уже убедились, что отсутствие значения допустимо, можно добавить ЕСЛИОШИБКА:

=ЕСЛИОШИБКА(ВПР(F2;$A$2:$D$5;4;ЛОЖЬ);"Код не найден")

Английский вариант:

=IFERROR(VLOOKUP(F2,$A$2:$D$5,4,FALSE),"Код не найден")

Так отчёт становится понятнее пользователю. Но ЕСЛИОШИБКА не должна скрывать систематическую проблему со справочником.

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

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

В таких случаях лучше использовать ПРОСМОТРX или связку ИНДЕКС + ПОИСКПОЗ.

Аргумент 4 означает четвёртый столбец внутри указанного диапазона, а не четвёртый столбец листа вообще.

Если кто-то вставит новый столбец внутри справочника, логика старой формулы может измениться. Это одна из причин, почему в современных версиях Excel часто удобнее ПРОСМОТРX: там диапазон поиска и диапазон результата задаются отдельно.

Для знакомого стабильного справочника ВПР остаётся понятным и рабочим инструментом.

ПРОСМОТРX удобнее, когда:

• нужно искать влево;
• структура таблицы часто меняется;
• хочется явно указать значение при отсутствии ключа;
• важно отделить диапазон поиска от диапазона результата.

Эквивалент нашего примера через ПРОСМОТРX:

=ПРОСМОТРX(F2;$A$2:$A$5;$D$2:$D$5;"Код не найден")

Если ПРОСМОТРX недоступен в вашей версии Excel, ВПР с точным совпадением остаётся нормальным решением.

Если в первом столбце справочника один и тот же код встречается несколько раз, ВПР возвращает первое найденное совпадение.

Это значит, что формула сама по себе не проверяет уникальность справочника. Перед использованием ВПР для клиентских ID, артикулов или табельных номеров полезно отдельно проверить дубли.

Если ключ по бизнес-правилам должен быть уникальным, два одинаковых значения — проблема данных, а не формулы.

Представьте еженедельную выгрузку продаж:

В справочнике отдельно хранятся лимиты клиентов. Формулу можно поставить в столбец Лимит и скопировать вниз:

=ВПР(A2;Справочник!$A$2:$D$500;4;ЛОЖЬ)

После этого легко добавить контроль:

• сумма продажи выше лимита;
• код клиента отсутствует в справочнике;
• в справочнике появились дубли;
• лимит пустой.

Так ВПР становится не учебной формулой, а частью реального контроля данных.

Не копируйте ВПР сразу на десятки тысяч строк. Сначала проверьте несколько контролируемых случаев:

Для одной строки полезно найти значение вручную и сравнить его с результатом формулы.