Функция ВПР в Excel: как сделать по шагам, пример формулы и почему ВПР выдаёт #Н/Д

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

По-русски она читается так: найди содержимое A2 в первом столбце диапазона A2:C500 на листе «Прайс» и верни значение из третьего столбца этого диапазона, причём только при точном совпадении. Почти все жалобы на ВПР сводятся к двум вещам из этой строки: в конце забыли написать ЛОЖЬ или не поставили знаки доллара в диапазоне, и при протягивании формула поехала вниз вместе с ячейками.

Как сделать ВПР: пример по шагам

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

1. На листе «Заказ» щёлкните ячейку C2 и наберите =ВПР(. Excel покажет подсказку с четырьмя аргументами.
2. Щёлкните A2, это искомое значение. Поставьте точку с запятой.
3. Перейдите на лист «Прайс» и выделите A2:C500 с запасом строк вниз. Сразу нажмите F4: ссылка превратится в $A$2:$C$500 и не съедет при копировании. На Mac вместо F4 работает ⌘+T.
4. Поставьте точку с запятой, введите 3 (цена в третьем столбце выделенного диапазона), ещё одну точку с запятой и ЛОЖЬ. Закройте скобку и нажмите Enter.
5. В C2 появилась цена. Наведите курсор на правый нижний угол ячейки и дважды щёлкните маленький квадрат (маркер заполнения): формула протянется вниз до конца данных в соседнем столбце.

Если формулы пугают, есть мастер. На вкладке «Формулы» нажмите «Вставить функцию» (или кнопку fx слева от строки формул), в списке категорий выберите «Ссылки и массивы», найдите ВПР и нажмите «ОК». Откроется окно «Аргументы функции» с четырьмя полями: щёлкните в любое, и под полями Excel коротко объяснит, что туда вписать. На Mac то же окно называется «Построитель формул».

ВПР в мастере функций

При русских региональных настройках Windows аргументы разделяются точкой с запятой. Формулы с запятыми из англоязычных инструкций Excel не примет, пока не замените запятые.

Четыре аргумента ВПР и главная ловушка

Полный вид функции: ВПР(искомое_значение; таблица; номер_столбца; интервальный_просмотр). Первый аргумент обычно ссылка на ячейку, но можно вписать и текст в прямых кавычках: "Иванов". Кавычки «ёлочкой», скопированные из Word, формула не поймёт.

Четыре аргумента ВПР

Таблица начинается со столбца, в котором ищем. Это жёсткое требование: ВПР просматривает только левый столбец диапазона, поэтому выделять нужно с него, даже если слева на листе есть другие столбцы. Номер столбца считается от левого края выделенного диапазона, левый равен 1. Если таблица начинается со столбца C, то цена из столбца E будет третьей.

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

Почему ВПР выдаёт #Н/Д

#Н/Д значит «не нашла». Чаще всего искомое в таблице есть, но для Excel оно выглядит иначе. Проверяйте в таком порядке.

Значения нет или ищете не в том столбце

Сначала убедитесь, что артикул действительно есть в прайсе: Ctrl+F по листу «Прайс». Нашёлся? Посмотрите, в каком он столбце. Если артикулы лежат в B, а диапазон начинается с A, ВПР ищет по столбцу A и честно ничего не находит. Диапазон должен начинаться ровно со столбца с искомыми значениями.

Числа сохранены как текст

Самая коварная причина, особенно после выгрузки из 1С, CRM или CSV. В одной таблице артикул 10245 записан числом, в другой текстом, и для Excel это разные значения. Признак: числа прижаты к левому краю ячейки, а в углу зелёный треугольник. Выделите ячейку и щёлкните значок с восклицательным знаком рядом с ней: в меню будет строка «Число сохранено как текст» и пункт «Преобразовать в число».

#Н/Д из-за числа-текста

Для целого столбца быстрее так: выделите его, задайте формат «Общий», затем «Данные» → «Текст по столбцам» и сразу «Готово». Excel перепишет содержимое, и текст станет числами. Если данные трогать нельзя, приведите тип прямо в формуле. Ищете текст в столбце чисел, добавьте два минуса перед ссылкой. Ищете число в столбце текста, приклейте пустую строку:

С ведущими нулями (артикул 004512) превращать в число нельзя, нули пропадут. Там приводите обе стороны к тексту. Как не потерять нули ещё при открытии файла, разобрано в статье про открытие CSV в Excel.

Лишние пробелы и неразрывный пробел

«Болт М8» и «Болт М8 » с пробелом в конце для ВПР разные строки. Проверить легко: =ДЛСТР(A2) покажет больше символов, чем видно глазами. Лечится функцией СЖПРОБЕЛЫ: в соседнем столбце напишите =СЖПРОБЕЛЫ(A2), протяните, скопируйте результат и вставьте поверх исходного столбца как значения.

Если СЖПРОБЕЛЫ не помогла, в данных неразрывный пробел с кодом 160. Он приходит из веб-страниц и некоторых выгрузок, и СЖПРОБЕЛЫ его не трогает. Сначала замените его обычным пробелом, потом чистите:

На Mac вместо СИМВОЛ(160) пишите ЮНИСИМВ(160): СИМВОЛ там берёт знаки из набора Macintosh, и код 160 означает другой символ.

Первая строка работает, остальные нет

Диапазон без знаков доллара. В C2 формула смотрит в A2:C500, в C3 уже в A3:C501, а к сотой строке верх прайса выпадает из поиска. Откройте любую ячейку с #Н/Д и посмотрите на диапазон в строке формул. Если он «уехал», вернитесь в C2, выделите в формуле диапазон, нажмите F4 и протяните заново.

Приблизительный режим

Если в конце формулы стоит ИСТИНА или четвёртого аргумента нет, ВПР выдаёт #Н/Д, когда искомое меньше самого маленького значения в первом столбце. А на неотсортированных данных бывает хуже: вместо ошибки чужая цена. Поставьте ЛОЖЬ.

Как заменить #Н/Д понятным текстом

Когда части артикулов в прайсе действительно нет, оберните формулу в ЕСНД:

Многие советуют ЕСЛИОШИБКА, и она тоже сработает. Но ЕСЛИОШИБКА глушит любые ошибки, включая #ССЫЛКА! после удаления столбца и #ИМЯ? от опечатки в названии функции. В итоге сломанная формула весь месяц пишет «нет в прайсе», и никто этого не замечает. ЕСНД прячет только «не найдено», остальные поломки остаются на виду.

Другие ошибки ВПР: #ССЫЛКА!, #ЗНАЧ!, #ИМЯ?

ВПР сломалась после вставки столбца

Номер столбца в ВПР записан числом и сам не меняется. Вставили в прайс столбец «Остаток» между названием и ценой, диапазон растянулся до D, а формула по-прежнему берёт третий столбец. Теперь это остаток, и в заказе вместо цен стоит количество штук на складе, причём без всякой ошибки, так что заметят это, скорее всего, только когда клиент удивится счёту. Про саму вставку строк и столбцов есть отдельная инструкция, а формулу лучше сразу сделать устойчивой.

Для этого номер столбца ищет функция ПОИСКПОЗ по заголовку:

ПОИСКПОЗ находит слово «Цена» в строке заголовков и возвращает его позицию. Строка заголовков должна начинаться с того же столбца, что и диапазон ВПР, иначе номер съедет. Зато столбцы теперь можно вставлять и переставлять как угодно.

ВПР с другого листа и из другой книги

С другим листом всё просто: перед диапазоном стоит имя листа и восклицательный знак, Прайс!$A$2:$C$500. Если в имени листа есть пробел, Excel возьмёт его в одинарные кавычки: 'Прайс поставщика'!$A$2:$C$500. Выделяйте диапазон мышью, тогда кавычки появятся сами.

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

Когда книгу-прайс закроете, Excel сам допишет в формулу полный путь к файлу, и считать она продолжит. При следующем открытии заказа Excel предупредит о связях с другими книгами: пока не разрешите обновление, в ячейках останутся последние сохранённые значения. Если прайс переименовали или перенесли в другую папку, путь правится не в каждой формуле, а один раз: «Данные» → «Ссылки в книге» → меню «…» у нужного файла → «Изменить источник». В Excel 2016 и 2019 это окно открывается через «Данные» → «Изменить связи».

ВПР по двум условиям

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

В заказе склейте искомое так же и ищите по ключу:

Разделитель «|» нужен, чтобы «Болт 12» + «5» и «Болт 1» + «25» не дали одинаковый ключ. Без него такие пары рано или поздно совпадут. В Excel 2021, 2024 и Microsoft 365 вспомогательный столбец не нужен, условия перемножаются прямо в ПРОСМОТРX:

Когда нужна ИСТИНА: поиск по шкале

Приблизительный режим придуман для порогов: скидка от количества, ставка от суммы, оценка от баллов. Пусть в F2:G5 шкала скидок: от 0 штук 0%, от 10 штук 5%, от 50 штук 10%, от 100 штук 15%. Формула для количества из B2:

На 49 штуках она вернёт 5%: берётся наибольший порог, который не больше искомого. С ЛОЖЬ здесь была бы #Н/Д, потому что ровно 49 в шкале нет. Два условия обязательны. Пороги отсортированы по возрастанию. Первая строка шкалы начинается с минимально возможного значения, обычно с нуля, иначе всё, что меньше первого порога, даст #Н/Д.

Поиск по части текста

Если в заказе «М8», а в прайсе «Болт М8 оцинкованный», помогут подстановочные знаки. Звёздочка заменяет любое количество символов:

Работает только в точном режиме и только с текстом. Вопросительный знак заменяет один символ, а тильда перед * или ? ищет сам символ. ВПР вернёт первую подходящую строку сверху, так что на коротких фрагментах вроде «М8» легко поймать не тот товар.

Когда ВПР не хватает: ИНДЕКС с ПОИСКПОЗ и ПРОСМОТРX

ВПР не умеет искать влево. Знаете название и хотите получить артикул, который стоит левее, она бессильна. Классический обход работает в любой версии Excel:

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

В Excel 2021, 2024 и Microsoft 365 есть ПРОСМОТРX, и она закрывает почти все неудобства ВПР. Ищет в любую сторону, по умолчанию точно, а текст на случай «не найдено» пишется прямо в формуле:

Шестой аргумент -1 заставляет искать снизу вверх и возвращать последнее совпадение, например последнюю цену из журнала закупок. ВПР так не умеет: она всегда отдаёт первую сверху строку. Если формулу ПРОСМОТРX копируете из справки Microsoft, проверьте последнюю букву. В русской справке местами набрана кириллическая «Х», а в Excel имя функции пишется с латинской X, и с кириллицей получите #ИМЯ?.

Ограничение одно, но важное. В Excel 2016 и 2019 функции ПРОСМОТРX нет. Если вы отправите файл коллеге со старой версией, у него перед функцией появится префикс _xlfn., а при пересчёте ячейка покажет #ИМЯ?. Для обмена с такими коллегами оставайтесь на ВПР и ИНДЕКС с ПОИСКПОЗ. Если старый Excel стоит у вас самих, смотрите чем отличаются версии Excel: ПРОСМОТРX и динамические массивы появились в 2021.

Сравнить версии Excel

Синтаксис, примеры и ограничения сверены со справкой Microsoft по функции ВПР и функции ПРОСМОТРX 8 октября 2026 года.

5.09 отзывов

Microsoft Excel 2024 — бессрочный ключ с привязкой

6 070 ₽5 970 ₽

В корзину

4.8833 отзыва

Microsoft Office 2024 Home — бессрочный ключ с привязкой

12 970 ₽11 970 ₽

В корзину

4.95479 отзывов

Microsoft 365 Персональный — подписка на 1 год для России

6 970 ₽

В корзину

5.09 отзывов

Microsoft Excel 2024 — бессрочный ключ с привязкой

6 070 ₽5 970 ₽

В корзину

4.8833 отзыва

Microsoft Office 2024 Home — бессрочный ключ с привязкой

12 970 ₽11 970 ₽

В корзину

4.95479 отзывов

Microsoft 365 Персональный — подписка на 1 год для России

6 970 ₽

В корзину

Дистрибутивы: Microsoft Excel 2024, Microsoft Office 2024 Home, Microsoft 365 Персональный (Personal).

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

ВПР различает большие и маленькие буквы?

Нет. «ИВАНОВ» и «Иванов» для ВПР одно и то же. Если регистр важен, нужна связка ИНДЕКС, ПОИСКПОЗ и СОВПАД.

Что вернёт ВПР, если искомое значение встречается в таблице несколько раз?

Первую сверху строку. Остальные совпадения она не видит. Последнее совпадение даёт ПРОСМОТРX с режимом поиска -1, а все сразу выводит функция ФИЛЬТР в Excel 2021 и новее.

Почему ВПР возвращает 0, хотя в прайсе пусто?

Ячейка-источник пустая, и ВПР показывает её как ноль. Чтобы пустое оставалось пустым, проверьте результат через ЕСЛИ: =ЕСЛИ(ВПР(A2;Прайс!$A$2:$C$500;3;ЛОЖЬ)="";"";ВПР(A2;Прайс!$A$2:$C$500;3;ЛОЖЬ)).

Вместо результата в ячейке виден текст формулы. Что не так?

Ячейка отформатирована как текст или в параметрах включён показ формул. Этот случай и ручной режим вычислений разобраны в статье Excel не считает формулы.

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

ГПР ищет по первой строке и возвращает значение из строки ниже, то есть работает с горизонтальными таблицами. Аргументы и правила те же. ПРОСМОТРX умеет и так, и так.

Как сделать ВПР в Excel на Mac?

Формула та же, с точкой с запятой. Знаки доллара ставятся сочетанием ⌘+T вместо F4, а мастер функций называется «Построитель формул».