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

Разберём ВПР на простом примере, объясним каждый аргумент, частые ошибки и покажем современную замену — функцию ПРОСМОТРX.

Коротко
  • Формула: =ВПР(что_ищем; где_ищем; номер_столбца; ЛОЖЬ). В английском Excel — VLOOKUP.
  • ВПР ищет значение только в первом столбце диапазона и возвращает данные из столбца правее.
  • Последний аргумент почти всегда ЛОЖЬ (или 0) — точное совпадение.
  • Закрепляйте диапазон знаками $ (клавиша F4), чтобы формула не «съехала» при копировании.
  • В Excel 2021, 2024 и Microsoft 365 удобнее ПРОСМОТРX: ищет в любую сторону и не требует номера столбца.

Пример: подставить цены из прайса

На листе «Прайс» в столбце A — названия товаров, в столбце B — цены. На листе «Заказы» в столбце B — товары, которые купили клиенты, а в столбце C нужно подставить цену каждого.

Пример функции ВПР в Excel: подстановка цены из прайс-листа по названию товара

  1. Встаньте в ячейку C2 на листе «Заказы».
  2. Введите формулу:
    =ВПР(B2;Прайс!$A$2:$B$100;2;ЛОЖЬ)
  3. Нажмите Enter — появится цена товара из B2.
  4. Протяните формулу вниз за маркер в правом нижнем углу ячейки (или дважды щёлкните по нему) — цены подставятся для всех строк.

Что означает каждый аргумент

  • Искомое значение (B2) — что ищем. Здесь — название товара из текущей строки.
  • Таблица (Прайс!$A$2:$B$100) — где ищем. Искомое значение должно быть в самом левом столбце этого диапазона. Знаки $ закрепляют диапазон: без них при протягивании формулы вниз он сдвигается на строку и последние товары перестают находиться.
  • Номер столбца (2) — из какого столбца диапазона взять результат. Счёт идёт от первого столбца диапазона, а не листа: A — это 1, B — 2.
  • Интервальный просмотр (ЛОЖЬ) — тип поиска. ЛОЖЬ или 0 — точное совпадение. ИСТИНА или пустое значение — приблизительный поиск, который работает правильно только на отсортированных данных и нужен, например, для шкал скидок. Если не уверены, всегда пишите ЛОЖЬ.

Чтобы быстро поставить $, выделите в строке формул ссылку на диапазон и нажмите F4.

В английской версии Excel и в Google Таблицах с английским интерфейсом формула выглядит так: =VLOOKUP(B2,Price!$A$2:$B$100,2,FALSE). Разделитель аргументов зависит от региональных настроек: в русских — точка с запятой, в английских — запятая.

Частые ошибки

Частые ошибки функции ВПР: #Н/Д, #ССЫЛКА!, неверный результат

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

Самая частая ошибка. Причины:

  • Лишние пробелы. «Чайник » с пробелом в конце и «Чайник» для Excel — разные значения. Уберите их функцией СЖПРОБЕЛЫ или через «Найти и заменить».
  • Числа, сохранённые как текст. Артикул 1024 в одной таблице может быть числом, а в другой — текстом (зелёный треугольник в углу ячейки). Приведите к одному формату: выделите ячейки → значок предупреждения → «Преобразовать в число».
  • Значения нет в первом столбце диапазона или диапазон не закреплён $ и съехал.
  • Товара действительно нет в прайсе. Тогда вместо ошибки удобно выводить понятный текст:
    =ЕСЛИОШИБКА(ВПР(B2;Прайс!$A$2:$B$100;2;ЛОЖЬ);"нет в прайсе")

#ССЫЛКА! — номер столбца больше, чем столбцов в диапазоне

Например, в диапазоне A:B всего два столбца, а вы указали 3. Расширьте диапазон или исправьте номер.

Неправильное значение без ошибки

Почти всегда это пропущенный четвёртый аргумент: без ЛОЖЬ ВПР ищет приблизительно и возвращает данные из «ближайшей» строки. Допишите ;ЛОЖЬ.

Формула сломалась после вставки столбца

Номер столбца в ВПР — просто число, и при вставке нового столбца в прайс он не меняется. Проверьте номер или используйте ПРОСМОТРX.

ПРОСМОТРX — современная замена ВПР

В Excel 2021, Excel 2024 и Microsoft 365 есть функция ПРОСМОТРX (XLOOKUP). Тот же пример:

=ПРОСМОТРX(B2;Прайс!$A$2:$A$100;Прайс!$B$2:$B$100;"нет в прайсе")

Аргументы: что ищем, где ищем (один столбец), откуда взять результат (один столбец) и что вывести, если ничего не найдено. Преимущества:

  • ищет точное совпадение по умолчанию — не нужно помнить про ЛОЖЬ;
  • столбец результата может быть левее столбца поиска;
  • не ломается при вставке столбцов;
  • сразу умеет выводить текст вместо #Н/Д.

Если файл будут открывать в Excel 2019 и старше, ПРОСМОТРX там не сработает — используйте ВПР или связку ИНДЕКС + ПОИСКПОЗ. В Google Таблицах есть обе функции: VLOOKUP и XLOOKUP.

Полезные приёмы

  • Ссылайтесь на целые столбцы (Прайс!$A:$B), если прайс пополняется, — тогда новые строки сразу попадут в поиск.
  • Оформите прайс как таблицу (Ctrl + T) — диапазон будет расширяться автоматически, а в формуле появятся понятные имена вроде Прайс[Цена].
  • Добавьте выпадающий список товаров в столбец B заказов — так названия будут совпадать с прайсом и ВПР не будет выдавать #Н/Д из-за опечаток.

Частые вопросы

Можно ли искать по двум условиям?

Да. Проще всего сделать вспомогательный столбец, который склеивает условия (=A2&"|"&B2), и искать по нему. В ПРОСМОТРX можно обойтись без него: =ПРОСМОТРX(1;(A:A=F2)*(B:B=G2);C:C) — здесь F2 и G2 содержат два условия поиска.

Чем ВПР отличается от ГПР?

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

© kak2.ru