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

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



Оставить комментарий