Написать
Главная / База знаний / Функция ВПР (VLOOKUP) в Excel

Функция ВПР (VLOOKUP) в Excel: примеры и разбор ошибок

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

Опубликовано

Синтаксис функции ВПР

Функция принимает четыре аргумента:

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

Пример использования ВПР

Допустим, в диапазоне A2:C100 есть таблица товаров: артикул в столбце A, название — в B, цена — в C. Чтобы по артикулу из ячейки E2 найти цену товара:

  • =ВПР(E2;A2:C100;3;0) — Excel ищет значение из E2 в столбце A и возвращает значение из третьего столбца найденной строки (то есть из столбца C — цену).

Номер столбца отсчитывается не от начала листа, а от начала указанного диапазона: если диапазон начинается со столбца A, то третий столбец диапазона — это столбец C.

Точное и приближённое совпадение

Последний аргумент определяет, что считать совпадением:

  • 0 или ЛОЖЬ — точное совпадение. Если значения нет в таблице совсем, ВПР вернёт ошибку #Н/Д. Это правильный выбор для поиска по кодам, артикулам, ID, ФИО и в подавляющем большинстве обычных задач;
  • 1 или ИСТИНА — приближённое совпадение. Первый столбец таблицы обязательно должен быть отсортирован по возрастанию — ВПР находит наибольшее значение, не превышающее искомое. Такой режим применяют, например, для расчёта скидки или ставки налога по диапазонам сумм.
Если четвёртый аргумент не указать вовсе, Excel по умолчанию использует приближённое совпадение (1) — это частая причина неожиданных результатов, когда пользователь ожидал точный поиск. Указывать 0 явно — хорошая привычка.

Как искать на другом листе или в другой книге

Диапазон таблицы (второй аргумент) может ссылаться на другой лист или книгу:

  • =ВПР(E2;Лист2!A2:C100;3;0) — поиск в диапазоне на листе «Лист2»;
  • =ВПР(E2;[Прайс.xlsx]Лист1!A2:C100;3;0) — поиск в другой открытой книге Excel (обе книги должны быть открыты одновременно, иначе Excel запросит их открыть или укажет полный путь к файлу).

Почему ВПР не ищет влево

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

Для поиска в любом направлении используют связку функций ИНДЕКС и ПОИСКПОЗ: ПОИСКПОЗ находит номер строки по искомому значению в любом столбце, а ИНДЕКС возвращает значение из любого другого столбца этой строки — независимо от того, левее он или правее.

Разбор ошибок ВПР

  • #Н/Д — самая частая ошибка, означает, что искомое значение не найдено в первом столбце диапазона. Причины: значения различаются невидимыми пробелами, разный тип данных (число как текст против настоящего числа), опечатка, или диапазон поиска не включает нужную строку;
  • #ССЫЛКА! — номер столбца (третий аргумент) больше, чем количество столбцов в указанном диапазоне. Часто возникает после удаления столбца из таблицы: диапазон сузился, а номер столбца в формуле остался прежним;
  • #ЗНАЧ! — номер столбца указан как текст или дробное число вместо целого положительного числа, либо искомое значение — ссылка на пустую или несуществующую ячейку.

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

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

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

Что означает последний аргумент ВПР — 0 или 1?

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

Почему ВПР не находит значение, если нужный столбец расположен левее искомого?

Так устроена сама функция: она всегда ищет значение в первом (самом левом) столбце заданного диапазона и возвращает результат из столбца правее на N позиций. Если нужный результат физически находится левее столбца с искомым значением, ВПР его не найдёт — для такой задачи используют комбинацию функций ИНДЕКС и ПОИСКПОЗ, у которой такого ограничения нет.

Как искать значение на другом листе или в другой книге Excel?

В качестве диапазона таблицы (второй аргумент ВПР) указывается ссылка с именем листа через восклицательный знак, например Лист2!A2:D100. Для поиска в другой открытой книге ссылка выглядит так: [Книга2.xlsx]Лист1!A2:D100.

Что дальше

Если ВПР выдаёт #Н/Д на значении, которое точно есть в таблице, — в первую очередь стоит проверить исходные данные на невидимые пробелы: подробный разбор в статье «Как убрать лишние пробелы в Excel».

Готовые таблицы с рабочими формулами поиска

В шаблонах на сайте формулы ВПР и связанные с ними диапазоны уже настроены и проверены — остаётся только подставить свои данные.

Смотреть инструменты