Рассматривая синтаксис функции ВПР (VLOOKUP), мы уже отмечали, что в случае, если поиск завершится неудачей, функция возвратит ошибку "#Н/Д"  (#N/A). Давайте постараемся вместе попробовать ответить на вопрос: «Почему функция ВПР не работает?»

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

Если функция ВПР не сработала так, как нам нужно, и мы видим сообщение об ошибке, то это ещё не значит, что мы сделали что-то неправильно.

Почему не работает? Разбираем наиболее типичные ошибки при использовании функции ВПР в Google таблицах.

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

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

Итак, разберём наиболее распространённые ошибки.

Ошибка при вводе данных

В этом случае ничего найти не удастся и вы увидите ошибку #Н/Д. Самое простое решение здесь очень часто оказывается самым верным. Быть может, вы просто ошиблись при вводе данных в ячейку поиска. Это вполне может случиться, в особенности если вы вводите значение поиска не в ячейку, а прямо в формулу.

Проверьте, правильно ли введены данные.

Ошибка при вводе функции.

Если вы видите сообщение об ошибке #ИМЯ? (#NAME?), то это означает, что при вводе названия самой функции ВПР вы допустили ошибку – перепутали или добавили лишнюю букву.

Проверьте синтаксис написания функции, и всё бедет в порядке.

Неверные ссылки в функции ВПР

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

К примеру, мы ищем цену бананов.

Ошибки в функции ВПР

Но в функции вы указали обычные (относительные) ссылки, забыв заменить их на абсолютные ($), и затем произвели какие-то действия с таблицей. К примеру, добавили столбец. В результате ваш диапазон поиска автоматически изменился и стал шире на 1 столбец.

неверные ссылки в ВПР

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

Важно! При изменении ссылок сообщение об ошибке часто не появляется. Поэтому будьте внимательны в использовании относительных и абсолютных ссылок!

Неверно указан параметр "сортировка" (is_sorted).

Включен поиск до первого приблизительного совпадения в отсортированном диапазоне (параметр is_sorted = TRUE), но на самом деле данные не отсортированы.

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

Столбец поиска не является первым слева столбцом диапазона поиска.

Часто забывают о том, что функция ВПР (VLOOKUP) ищет совпадающие значения только в первом слева столбце диапазона, который вы указали.

Если ваши значения поиска находятся не в первом, а во втором или другом столбце, то функция ВПР не будет работать и вы вновь увидите сообщение #Н/Д. К примеру, вы ищете цену по артикулу товара, но в первом столбце прайс-листа у вас находятся наименования.

ВПР ищет только в первом столбце

Это важное ограничение функции ВПР нельзя забывать.

Несовпадение форматов данных.

Формат ячейки, откуда берется искомое значение наименования (например D3 в нашем случае), и формат ячеек первого столбца (A3:A21) из диапазона поиска отличаются (например, числовой и текстовый). Этот случай особенно часто встречается при использовании вместо текстовых наименований числовых кодов (номера счетов, артикулы, идентификаторы, даты и т.п.)

несовпадение типов данных в функции ВПР

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

Как видите, с виду записи одинаковы, однако в ячейке D6 значение сохранено как число, а в ячейке A10 – как текст. Текст не может быть равен числу, поэтому функция возвращает ошибку.

Как решить эту проблему со случайной ошибкой в формате записи? Можно использовать функцию ТЕКСТ, которая преобразует любые значения в текстовый вид.

вложенные функции

Теперь функция ВПР нашей Google таблицы выглядит так:

=ВПР(ТЕКСТ(D6;"#");$A$3:$B$21;2;ЛОЖЬ)

Обычную ссылку на ячейку D6 мы заменяем функцией

TEКСТ(D6;"#")

 Первый аргумент – это сама ячейка, а второй аргумент означает формат, в который мы будем преобразовывать значение этой ячейки. В данном случае - в текстовый.

Как видите, использование вложенной функции помогло решить проблему с несовпадением форматов данных.

Если же такая ошибка только одна, то можно просто исправить формат данных в ячейке. Нажмите Меню -> Формат-> Число-> Обычный текст. Значение в ячейке будет преобразовано в текст.

Как видите, ошибка исчезла, поскольку теперь текстовое значение сравнивается с таким же текстовым значением.

Лишние пробелы и непечатаемые знаки.

Функция не может найти нужного значения, потому что в коде присутствуют пробелы или невидимые непечатаемые знаки (перенос строки и т.п.).

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

функция ВПР не работает

Такая ошибка может встеречаться довольно часто, и обнаружить её визуально практически невозможно.

К примеру, если наименование состоит из 2 слов, то вполне возможно случайное появление лишнего пробела между этими словами.

Могут быть также случайно добавлены символы, невидимые на экране и на печати (табуляция, перевод строки и т.д.). Визуально определить такие ошибки практически невозможно.

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

=ВПР(D4;$A$3:$B$21;2;ЛОЖЬ)

вводим формулу

=ВПР(СЖПРОБЕЛЫ(ПЕЧСИМВ(D4));$A$3:$B$21;2;ЛОЖЬ)

или

=VLOOKUP(TRIM(CLEAN(D4)),$A$3:$B$21,2,FALSE)

функция ВПР работает без ошибок

Функция СЖПРОБЕЛЫ (TRIM)  удаляет пробелы, а функция ПЕЧСИМВ (CLEAN) удаляет все непечатаемые и невидимые символы из текстового значения.

Эту комбинацию функций рекомендуем применять всегда «на всякий случай», чтобы подстраховать себя от случайных ошибок.

Неправильно указан номер столбца.

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

Номер столбца не может быть меньше 1 и не может быть больше, чем количество столбцов в указанном для поиска диапазоне. Если номер столбца указан неверно, то ВПР возвращает ошибку #VALUE!

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

И в любом случае – номер столбца должен быть больше 1. Вряд ли кто-то из вас будет руками вводить значение 0 или -1, но если вы получаете номер столбца при помощи каких-то функций, вложенных в ВПР, то здесь-то и может быть ошибка.

Ошибка в ссылке на данные из другой таблицы

Мы с вами говорили о том, что диапазон поиска может быть расположен в другой таблице или даже в другом файле Google таблиц.

Если вы случайно допустили ошибку при указании ссылки на эти данные, то увидите сообщение об ошибке #ССЫЛКА! (#REF!)

Это будет означать, что вы ссылаетесь на неверный диапазон, который программа найти не может.

После изменения таблицы функция перестала работать

Вы сделали все правильно и до тех пор, пока вы не вставили несколько строк или стрлбцов в вашу таблицу, функция работала верно.

Теперь же появилась ошибка #Н/Д. В чем дело?

А дело всё в том, что вы не использовали в функции ВПР абсолютные ссылки, не добавляли к адресам ячеек, строк и столбцов знак $. В результате после добавления (или удаления) строк или столбцов ваши ссылки изменились и формула перестала работать.

Постарайтесь отменить слеланные изменения, благо Google таблицы хранят всю историю изменений вашей таблицы.

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

Как убрать сообщение об ошибке?

Сообщения об ошибке функции ВПР (VLOOKUP) не только показывают, что функция ВПР не работает, но и еще совсем не украшают вашу таблицу. Поэтому, видя сообщение об ошибке, вы, конечно же, захотите от них избавиться.

Важно! Но прежде чем приступать к таким радикальным мерам, всё же проверьте – а всё ли вы сделали правильно?

Итак, что нужно сделать прежде всего, увидев сообщение “#Н/Д”.

Во-первых, проверьте адрес диапазона поиска. Действительно ли то значение, которое вы ищете, может находиться в первом столбце диапазона.

Во-вторых, проверьте правильно ли вы указали тип параметра "сортировка" (is_sorted): ИСТИНА или ЛОЖЬ.

В-третьих, проверьте возможные несовпадения форматов, наличие лишних пробелов в условии поиска. Используйте рекомендации, которые мы давали выше.

Если вы все сделали правильно, но диапазоне поиска всё же нет нужного значения, дополните формулу функцией  ЕСЛИОШИБКА (IFERROR). Например, так:

=ЕСЛИОШИБКА(ВПР(D9;$A$3:$B$21;2;ЛОЖЬ);"Не найдено")

или

=IFERROR(VLOOKUP(D9,$A$3:$B$21,2,FALSE),"No items")

обработка ошибок функции ВПР

Синтаксис функции ЕСЛИОШИБКА очень простой. Первый аргумент – это выражение, значение которого мы проверяем на возниконвение ошибки.

Если ошибки нет, товозвращается значение этого выражения. Если же возникает ошибка, то функция возвращает значение второго аргумента. В данном случае в ячейке F9 будет указано “Не найдено”.

Согласитесь, это гораздо более красиво и информативно, чем стандартное сообщение об ошибке.