Поиск и устранение ошибок в функции ВПР / VLOOKUP в Excel

Функция ВПР / VLOOKUP долгое время была незаменимым инструментом для поиска данных в Excel. Однако даже опытные пользователи сталкиваются с ошибками, самой частой из которых является #Н/Д. В этой статье мы разберем основные причины сбоев ВПР и предоставим пошаговые инструкции по их устранению.

Если вы разбираете формулы в Excel с нуля или хотите ускорить расчеты, здесь показано, как использовать функции в экселе на понятных и рабочих примерах.

Допустим, у вас есть список артикулов товаров в столбце A, а описание к ним находится в справочной таблице в столбцах F и G. Ваша задача — подставить описания с помощью ВПР.

Правильная формула для точного поиска выглядит так, с обязательным аргументом ЛОЖЬ (или 0) в конце:

=ВПР(A2;$F$3:$G$30;2;ЛОЖЬ)

=VLOOKUP(A2,$F$3:$G$30,2,FALSE)

Обратите внимание на абсолютные ссылки ($) для таблицы поиска, чтобы они не менялись при копировании формулы.

Основные причины ошибок #Н/Д и их решения

После применения формулы отсортируйте исходные данные по столбцу с ВПР по убыванию (Я→А). Все ошибки #Н/Д окажутся вверху, что упростит анализ.

1. Лишние пробелы в данных

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

Нажмите F2 для перехода в режим редактирования ячейки. Курсор, стоящий далеко от последнего символа, указывает на наличие пробелов.

1. Используйте функцию СЖПРОБЕЛЫ (СЖПРОБЕЛЫ / TRIM) внутри формулы ВПР. Она удаляет все лишние пробелы, кроме одиночных между словами.
   Русская версия:

   =ВПР(СЖПРОБЕЛЫ(A2);$F$3:$G$30;2;ЛОЖЬ)

   English version:

   =VLOOKUP(TRIM(A2),$F$3:$G$30,2,FALSE)

2. Если пробелы в справочной таблице (столбец F), создайте вспомогательный столбец с формулой Русская версия: =СЖПРОБЕЛЫ(F3)English version: =TRIM(F3), скопируйте его значения и вставьте обратно в столбец F.

2. Несоответствие типов данных (текст vs число)

ВПР не найдет соответствие, если ищет текстовое значение «4399» в столбце с реальными числами 4399, и наоборот.

1. Преобразуйте «текстовые» числа в реальные с помощью инструмента «Текст по столбцам»:
   • Выделите столбец с текстовыми числами.
   • Нажмите Alt+D, затем E, затем F (или через меню «Данные» → «Текст по столбцам» → «Готово»).
2. Исправьте формулу, не меняя данные:
   • Если ищете текст в числах: Русская версия: =ВПР(A2*1; ...)English version: =VLOOKUP(A2*1, ...) или Русская версия: =ВПР(ЗНАЧЕН(A2); ...)English version: =VLOOKUP(VALUE(A2), ...).
   • Если ищете число в тексте: Русская версия: =ВПР(A2 & ""; ...)English version: =VLOOKUP(A2 &, "", ...) или Русская версия: =ВПР(ТЕКСТ(A2;"0"); ...)English version: =VLOOKUP(TEXT(A2,"0"), ...).

Заключение и лучшие практики

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

• Единообразие типов данных (текст/число) в исходном списке и таблице поиска.
• Отсутствие лишних пробелов, используя СЖПРОБЕЛЫ или визуальную проверку через F2.
• Абсолютные ссылки на таблицу поиска или использование именованных диапазонов.

Освоив эти методы поиска неисправностей, вы сможете уверенно использовать ВПР для решения самых разных задач по поиску и сопоставлению данных в Excel.