Описать задачу
Функции Excel › ВПР › ВПР не работает

ВПР не работает: 6 причин #Н/Д и неверных значений — и как исправить

Редакция Формулника · обновлено · примеры проверены пересчётом LibreOffice 26.8 на сервере Формулника · как мы проверяем

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

Проверить свою формулу

Вставьте свою формулу ВПР — Формулник найдёт причину по правилам и даст исправленную запись.

Быстрая проверка

  1. Лишний пробел в искомом значении
  2. Число сохранено как текст
  3. Искомый столбец не первый
  4. Диапазон съехал, когда формулу протянули
  5. Номер столбца больше ширины диапазона
  6. Не указан последний аргумент — неверное число без ошибки

1. Лишний пробел в искомом значении

Артикул скопировали из письма или 1С вместе с пробелом в конце: «ДР-1100 » и «ДР-1100» для Excel — разные значения, точного совпадения нет, и ВПР отвечает #Н/Д. Пробел не видно глазами — его выдаёт ДЛСТР: длина на 1 больше.

ABCDEF
1АртикулТоварЦенаИщем
2УШМ-0915Болгарка4750ДР-1100 #Н/Д
3ДР-1100Дрель3900
4ПЕР-0700Перфоратор8200
Было → #Н/Д
=ВПР(E2;A2:C4;3;0)
Стало → 3 900
=ВПР(СЖПРОБЕЛЫ(E2);A2:C4;3;0)
=VLOOKUP(TRIM(E2),A2:C4,3,0)

Что сделать. Оберните искомое значение в СЖПРОБЕЛЫ — функция уберёт пробелы по краям. Если пробелы в самом прайсе, очистите столбец: рядом =СЖПРОБЕЛЫ(A2), протяните, скопируйте и вставьте как значения.

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

В прайсе код 200 — число, а в заказе «200» — текст (выгрузка, апостроф, формат «Текстовый»; в углу ячейки зелёный треугольник). Excel не считает их равными и отвечает #Н/Д. LibreOffice здесь мягче и находит — поэтому файл «работает у коллеги», а у вас нет.

ABCDE
1КодЦенаИщем
2100500200#Н/Д
3200700
4300900
Было → #Н/Д
=ВПР(D2;A2:B4;2;0)
Стало → 700
=ВПР(ЗНАЧЕН(D2);A2:B4;2;0)
=VLOOKUP(VALUE(D2),A2:B4,2,0)

Что сделать. Превратите искомое значение в число функцией ЗНАЧЕН. Или исправьте сам столбец: выделите его → жёлтый значок с восклицательным знаком → «Преобразовать в число».

3. Искомый столбец не первый

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

ABCDEF
1АртикулТоварЦенаТовар
2УШМ-0915Болгарка4750Дрель#Н/Д
3ДР-1100Дрель3900
4ПЕР-0700Перфоратор8200
Было → #Н/Д
=ВПР(E2;A2:C4;1;0)
Стало → ДР-1100
=ИНДЕКС(A2:A4;ПОИСКПОЗ(E2;B2:B4;0))
=INDEX(A2:A4,MATCH(E2,B2:B4,0))

Что сделать. Используйте связку ИНДЕКС и ПОИСКПОЗ: ПОИСКПОЗ находит строку по любому столбцу, ИНДЕКС берёт значение из любого другого. В Excel 2021/365 и Яндекс Таблицах проще ПРОСМОТРX.

4. Диапазон съехал, когда формулу протянули

Формулу написали в первой строке без знаков $ и протянули вниз. В третьей строке диапазон стал A3:C5 — первая строка прайса из поиска выпала, и для болгарки ВПР отвечает #Н/Д.

ABCDEF
1АртикулТоварЦенаИщем
2УШМ-0915Болгарка4750ДР-1100
3ДР-1100Дрель3900УШМ-0915#Н/Д
4ПЕР-0700Перфоратор8200
Было → #Н/Д
=ВПР(E3;A3:C5;3;0)
Стало → 4 750
=ВПР(E3;$A$2:$C$4;3;0)
=VLOOKUP(E3,$A$2:$C$4,3,0)

Что сделать. Закрепите диапазон таблицы знаками $ (клавиша F4 после выделения адреса) и протяните заново. Ещё надёжнее — сделать прайс «умной таблицей» (Ctrl+T): ссылка вида Прайс[#Данные] не съезжает.

5. Номер столбца больше ширины диапазона

Третий аргумент — номер столбца внутри выделенного диапазона, а не номер столбца листа. В диапазоне A:B всего два столбца, а просим третий — Excel отвечает #ССЫЛКА!.

ABCDEF
1АртикулТоварЦенаИщем
2УШМ-0915Болгарка4750ДР-1100#ССЫЛКА!
3ДР-1100Дрель3900
4ПЕР-0700Перфоратор8200
Было → #ССЫЛКА!
=ВПР(E2;A2:B4;3;0)
Стало → 3 900
=ВПР(E2;A2:C4;3;0)
=VLOOKUP(E2,A2:C4,3,0)

Что сделать. Расширьте диапазон, чтобы он захватывал нужный столбец, или уменьшите номер. Номер считается от первого столбца диапазона: A — 1, B — 2, C — 3.

6. Не указан последний аргумент — неверное число без ошибки

Без четвёртого аргумента ВПР ищет «приблизительно»: берёт ближайшее меньшее значение. Кода 150 в прайсе нет, но ВПР молча вернёт цену кода 100. Это самая опасная причина — ошибки нет, а число неверное.

ABCDE
1КодЦенаИщем
2100500150500
3200700
4300900
Было → 500
=ВПР(D2;A2:B4;2)
Стало → нет в прайсе
=ЕСЛИОШИБКА(ВПР(D2;A2:B4;2;0);"нет в прайсе")
=IFERROR(VLOOKUP(D2,A2:B4,2,0),"нет в прайсе")

Что сделать. Всегда ставьте последним аргументом 0 (ЛОЖЬ) — точное совпадение. А чтобы вместо #Н/Д для отсутствующих кодов было понятное слово, оберните формулу в ЕСЛИОШИБКА.

Если причин несколько

Исправляйте по порядку: сначала 0 в конце (иначе ошибка спрячется), затем закрепите диапазон $, потом проверьте пробелы и формат чисел. Чтобы вместо #Н/Д для отсутствующих значений было понятное слово — ЕСЛИОШИБКА. Подробно о функции — ВПР с примером, замены — ИНДЕКС + ПОИСКПОЗ и ПРОСМОТРX.

Вопросы

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

Чаще всего — лишний пробел или число, сохранённое как текст. Проверьте ДЛСТР у обеих ячеек и формат ячеек; формулы исправления — в разделах выше.

Почему ВПР возвращает не то значение?

Не указан последний аргумент 0: без него поиск приблизительный и берёт ближайшее меньшее значение. Допишите 0 — =ВПР(D2;A2:B4;2;0).

Почему ВПР не работает при протягивании?

Диапазон таблицы не закреплён знаками $ и съезжает вниз вместе с формулой. Нужно $A$2:$C$4.

Чем заменить ВПР?

ИНДЕКС + ПОИСКПОЗ работают в любой версии Excel и ищут в любую сторону; ПРОСМОТРX — в Excel 2021/365, Яндекс Таблицах и Google Таблицах.