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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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