Если ВПР возвращает ошибку: как исправить

Здравствуйте. Думаю, каждый сталкивался с ситуацией, когда вы используете ВПР, но что-то идёт не по плану, и вместо нужного результата появляется ошибка. Сегодня разбираемся, какие могут быть причины этого, и пути возможного исправления

Формула возвращает #Н/Д

Это самый распространенный случай, с него и начнем. Ошибка #Н/Д говорит, что функция не смогла найти искомое значение.

Способы исправления:

1. Возможно, искомой ячейки действительно нет в исходнике. Попробуйте проверить вручную. Не нашли — выбирайте из двух вариантов решения:
   • Добавить искомое значение в таблицу и повторить поиск
   • Если вы копируете формулу в различные ячейки – зафиксируйте используемые ссылки, чтобы они не изменялись при копировании. Больше о «заморозке ссылок» — здесь
2. Если отсутствие необходимых данных допустимо, обработайте ошибку с помощью специальных функций. Например, обернуть ВПР в обработчик:

   =ЕСНД(ВПР(G2;A3:B21;2;0);«Не найдено»)

   =ЕСЛИОШИБКА(ВПР(G2;A3:B21;2;0);«Не найдено»)

   Функция ЕСНД вернет значение «Не найдено», если ВПР вернул #Н/Д. ЕСЛИОШИБКА – даст результат «Не найдено», когда внутри любая ошибка, начинающаяся со знака #

Формула возвращает #ИМЯ?

Такая ошибка сообщает, что вы используете несуществующее или недопустимое имя.

Решение проблемы:

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

Ошибка #ССЫЛКА!

Такая ошибка говорит, что функция ссылается на несуществующие ячейки. Например, в формуле не закреплён массив для поиска, и при копировании он вышел за рамки листа, левее столбца A, выше первой строки и т.п.

Чтобы исправить проблему, проверьте формулу, укажите верные ссылки и зафиксируйте их знаками $. Выше я уже дал ссылку на статью об этом.

Еще одна возможная причина – вы пытаетесь вывести номер столбца, больший, чем ширина исходной таблицы. Проверьте и укажите номер колонки в пределах размеров исходника.

Возвращается #ЗНАЧ!

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

ВПР вычисляет неверный результат

Такое тоже бывает, функция отрабатывает без ошибок, но результат очевидно не тот, что вы ожидали. Вероятные причины:

• Вы указали не тот номер столбца для вывода. Проверьте и исправьте. В подсчете номера колонки участвуют и скрытые столбцы!
• Искомое значение в таблице встречается несколько раз. Функция возвращает первое найденное значение, которое вы можете и не ждать. Отсортируйте таблицу в нужном порядке, удалите дубликаты, или обобщите данные с помощью сводной таблицы. После этого повторите поиск.

Это все ошибки, с которыми я сталкивался в практике. Если вам не удалось исправить ошибку с помощью этих советов – пишите комментарии, будем разбираться вместе!