ВПР не работает: 6 причин #Н/Д и неверных значений — и как исправить
Почти всегда ВПР ломается по одной из шести причин. У каждой ниже — маленькая таблица, формула «было», которая выдаёт ошибку или неверное число, и формула «стало». Обе посчитаны на сервере: так, как их покажет Excel.
Быстрая проверка
- Лишний пробел в искомом значении
- Число сохранено как текст
- Искомый столбец не первый
- Диапазон съехал, когда формулу протянули
- Номер столбца больше ширины диапазона
- Не указан последний аргумент — неверное число без ошибки
1. Лишний пробел в искомом значении
Артикул скопировали из письма или 1С вместе с пробелом в конце: «ДР-1100 » и «ДР-1100» для Excel — разные значения, точного совпадения нет, и ВПР отвечает #Н/Д. Пробел не видно глазами — его выдаёт ДЛСТР: длина на 1 больше.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Ищем | ||
| 2 | УШМ-0915 | Болгарка | 4750 | ДР-1100 | #Н/Д | |
| 3 | ДР-1100 | Дрель | 3900 | |||
| 4 | ПЕР-0700 | Перфоратор | 8200 |
Что сделать. Оберните искомое значение в СЖПРОБЕЛЫ — функция уберёт пробелы по краям. Если пробелы в самом прайсе, очистите столбец: рядом =СЖПРОБЕЛЫ(A2), протяните, скопируйте и вставьте как значения.
- Неразрывный пробел (из сайтов и 1С) СЖПРОБЕЛЫ не убирает — замените его: =ПОДСТАВИТЬ(E2;СИМВОЛ(160);"").
2. Число сохранено как текст
В прайсе код 200 — число, а в заказе «200» — текст (выгрузка, апостроф, формат «Текстовый»; в углу ячейки зелёный треугольник). Excel не считает их равными и отвечает #Н/Д. LibreOffice здесь мягче и находит — поэтому файл «работает у коллеги», а у вас нет.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Код | Цена | Ищем | ||
| 2 | 100 | 500 | 200 | #Н/Д | |
| 3 | 200 | 700 | |||
| 4 | 300 | 900 |
Что сделать. Превратите искомое значение в число функцией ЗНАЧЕН. Или исправьте сам столбец: выделите его → жёлтый значок с восклицательным знаком → «Преобразовать в число».
- Обратный случай — числа в заказе, а в прайсе текст: =ВПР(ТЕКСТ(D2;"0");A2:B4;2;0).
3. Искомый столбец не первый
ВПР ищет только в первом столбце диапазона и возвращает значения правее. Если нужно по названию товара найти артикул, а артикул левее названия, ВПР не сможет — будет #Н/Д.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Товар | ||
| 2 | УШМ-0915 | Болгарка | 4750 | Дрель | #Н/Д | |
| 3 | ДР-1100 | Дрель | 3900 | |||
| 4 | ПЕР-0700 | Перфоратор | 8200 |
Что сделать. Используйте связку ИНДЕКС и ПОИСКПОЗ: ПОИСКПОЗ находит строку по любому столбцу, ИНДЕКС берёт значение из любого другого. В Excel 2021/365 и Яндекс Таблицах проще ПРОСМОТРX.
- =ПРОСМОТРX(E2;B2:B4;A2:A4) — Excel 2021, 365, Яндекс Таблицы, Google Таблицы.
4. Диапазон съехал, когда формулу протянули
Формулу написали в первой строке без знаков $ и протянули вниз. В третьей строке диапазон стал A3:C5 — первая строка прайса из поиска выпала, и для болгарки ВПР отвечает #Н/Д.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Ищем | ||
| 2 | УШМ-0915 | Болгарка | 4750 | ДР-1100 | ||
| 3 | ДР-1100 | Дрель | 3900 | УШМ-0915 | #Н/Д | |
| 4 | ПЕР-0700 | Перфоратор | 8200 |
Что сделать. Закрепите диапазон таблицы знаками $ (клавиша F4 после выделения адреса) и протяните заново. Ещё надёжнее — сделать прайс «умной таблицей» (Ctrl+T): ссылка вида Прайс[#Данные] не съезжает.
- Ссылка на весь столбец $A:$C тоже не съезжает, но в больших книгах считается медленнее.
5. Номер столбца больше ширины диапазона
Третий аргумент — номер столбца внутри выделенного диапазона, а не номер столбца листа. В диапазоне A:B всего два столбца, а просим третий — Excel отвечает #ССЫЛКА!.
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Артикул | Товар | Цена | Ищем | ||
| 2 | УШМ-0915 | Болгарка | 4750 | ДР-1100 | #ССЫЛКА! | |
| 3 | ДР-1100 | Дрель | 3900 | |||
| 4 | ПЕР-0700 | Перфоратор | 8200 |
Что сделать. Расширьте диапазон, чтобы он захватывал нужный столбец, или уменьшите номер. Номер считается от первого столбца диапазона: A — 1, B — 2, C — 3.
- Чтобы номер не сбивался при вставке столбцов, найдите его по заголовку: ПОИСКПОЗ("Цена";A1:C1;0).
6. Не указан последний аргумент — неверное число без ошибки
Без четвёртого аргумента ВПР ищет «приблизительно»: берёт ближайшее меньшее значение. Кода 150 в прайсе нет, но ВПР молча вернёт цену кода 100. Это самая опасная причина — ошибки нет, а число неверное.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Код | Цена | Ищем | ||
| 2 | 100 | 500 | 150 | 500 | |
| 3 | 200 | 700 | |||
| 4 | 300 | 900 |
Что сделать. Всегда ставьте последним аргументом 0 (ЛОЖЬ) — точное совпадение. А чтобы вместо #Н/Д для отсутствующих кодов было понятное слово, оберните формулу в ЕСЛИОШИБКА.
- Приблизительный поиск нужен только для шкал (скидка от суммы, налог по порогам) — и тогда первый столбец должен быть отсортирован по возрастанию.
Если причин несколько
Исправляйте по порядку: сначала 0 в конце (иначе ошибка спрячется), затем закрепите диапазон $, потом проверьте пробелы и формат чисел. Чтобы вместо #Н/Д для отсутствующих значений было понятное слово — ЕСЛИОШИБКА. Подробно о функции — ВПР с примером, замены — ИНДЕКС + ПОИСКПОЗ и ПРОСМОТРX.
Вопросы
Почему ВПР выдаёт #Н/Д, хотя значение есть?
Чаще всего — лишний пробел или число, сохранённое как текст. Проверьте ДЛСТР у обеих ячеек и формат ячеек; формулы исправления — в разделах выше.
Почему ВПР возвращает не то значение?
Не указан последний аргумент 0: без него поиск приблизительный и берёт ближайшее меньшее значение. Допишите 0 — =ВПР(D2;A2:B4;2;0).
Почему ВПР не работает при протягивании?
Диапазон таблицы не закреплён знаками $ и съезжает вниз вместе с формулой. Нужно $A$2:$C$4.
Чем заменить ВПР?
ИНДЕКС + ПОИСКПОЗ работают в любой версии Excel и ищут в любую сторону; ПРОСМОТРX — в Excel 2021/365, Яндекс Таблицах и Google Таблицах.