ВПР (VLOOKUP): поиск и подтягивание данных
Содержание курса
Диагностика #Н/Д и #ССЫЛКА! в формулах ВПР
ВПР возвращает два характерных вида ошибок, и по тексту ошибки сразу понятно, где искать причину.
#Н/Д — искомое значение не найдено
В режиме точного совпадения эта ошибка означает, что подходящее точное значение не найдено в первом столбце диапазона. Типичные причины:
- Опечатка в символах ключа. Регистр сам по себе причиной не является: обычный ВПР в Excel и Google Sheets не различает заглавные и строчные буквы.
- Лишний пробел: значение выглядит одинаково, но в одной ячейке после текста стоит пробел. Проверьте функцией
ДЛСТР()— длина строк должна совпадать. - Несовпадение типов данных: в таблице заказов код товара
101— это число, а в справочнике тот же код хранится как текст«101». Для Excel они разные, и точного совпадения не будет. Исправление: привести оба поля к одному типу — либо текст, либо число. - Диапазон начинается не с того столбца: если вы случайно взяли в диапазон столбец, где не хранятся ключи, поиск идёт не по тому полю.
Диагностика #Н/Д за два шага:
- Проверьте, что значение в первом аргументе и значение в первом столбце диапазона совпадают по типу (оба числа или оба текст) и по содержимому (нет лишних пробелов, непечатаемых символов или других опечаток).
- Убедитесь, что диапазон начинается именно со столбца-ключа.
#ССЫЛКА! — номер столбца выходит за границу диапазона
Эта ошибка проще по диагностике. Если во втором аргументе диапазон из двух столбцов, а в третьем аргументе стоит 3 — ВПР не может вернуть данные из несуществующего третьего столбца.
Результат: #ССЫЛКА!.
Пример сломанной формулы:
=ВПР(B2; $A$2:$B$100; 3; 0)
Диапазон A:B — два столбца. Третий столбец в нём отсутствует.
Исправление — либо расширить диапазон:
=ВПР(B2; $A$2:$C$100; 3; 0)
либо уменьшить номер столбца до числа, которое не превышает ширину диапазона:
=ВПР(B2; $A$2:$B$100; 2; 0)
Обе ошибки решаются без изменения логики формулы — только проверкой соответствия данных и параметров диапазона.
Закрепите тему на задачах
Проверьте, что столбец поиска находится первым в выбранном диапазоне, а для точного совпадения задан ЛОЖЬ. Число и похожая текстовая строка могут не совпасть.
ВПР в Excel: точное совпадение и закрепление диапазона
Откройте условие и решите задачу перед чтением следующей темы. В задачах на код проверяется программа, в тестовых заданиях — выбранный ответ. Для последовательного обучения вернитесь в программу бесплатного курса.