Шаг 1: Проверьте искомое значение

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

• Визуальная проверка: Внимательно сравните искомое значение с данными в столбце поиска.
• Редактирование ячейки: Выделите ячейку с искомым значением и нажмите F2, чтобы увидеть ее содержимое без форматирования.

=ВПР(ПРОБЕЛЫ(ОЧИСТИТЬ(A2)); B:C; 2; ЛОЖЬ)

Шаг 2: Проверьте диапазон поиска

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

• Правильность диапазона: Убедитесь, что диапазон охватывает все необходимые данные и, главное, что искомое значение находится в первом столбце этого диапазона. ВПР всегда ищет только в первом столбце.
• Абсолютные ссылки: Если вы протягиваете формулу ВПР, убедитесь, что диапазон поиска зафиксирован абсолютными ссылками (например,

  $B$2:$C$10

  ). Выделите диапазон в формуле и нажмите F4 для быстрого преобразования.

• Расширение диапазона: Возможно, новые данные были добавлены за пределами вашего текущего диапазона. Расширьте его или используйте именованные диапазоны. Если вы копируете листы, убедитесь, что диапазоны скопированы корректно. Подробнее об этом читайте в статье Как скопировать лист в другую таблицу с форматами: Google Sheets & Excel.

Шаг 3: Проверьте номер столбца

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

• Корректный номер: Убедитесь, что номер столбца соответствует столбцу, из которого вы хотите получить данные, относительно начала диапазона поиска. Например, если диапазон

  B2:D10

  , а вам нужно значение из столбца D, номер столбца будет 3 (B=1, C=2, D=3).

• В пределах диапазона: Номер столбца не может быть больше общего количества столбцов в вашем диапазоне.

Шаг 4: Проверьте тип сопоставления (интервальный просмотр)

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

=ВПР(искомое_значение; диапазон_таблицы; номер_столбца; ЛОЖЬ)

• ЛОЖЬ (FALSE) или 0: Ищет точное совпадение. Если его нет, возвращает #Н/Д. Это наиболее безопасный вариант.
• ИСТИНА (TRUE) или 1 (или пропущено): Ищет приблизительное совпадение. Требует, чтобы первый столбец диапазона поиска был отсортирован по возрастанию. Если данные не отсортированы, ВПР может вернуть некорректное значение или #Н/Д, даже если точное совпадение существует.

Шаг 5: Проверьте форматы данных

Одна из самых коварных причин #Н/Д — несоответствие форматов данных между искомым значением и данными в столбце поиска. Например, число, отформатированное как текст, не будет найдено среди чисел.

• Проверка формата: Используйте функцию

  =ТИП(ЯЧЕЙКА)

  (TYPE) для проверки типа данных. 1 — число, 2 — текст.

• Преобразование текста в число:
  • Выделите столбец, перейдите в ‘Данные’ -> ‘Текст по столбцам’ -> ‘Готово’.
  • Умножьте на 1:

    =ВПР(A2*1; B:C; 2; ЛОЖЬ)

    или

    =ВПР(ЗНАЧЕН(A2); B:C; 2; ЛОЖЬ)

    .

• Преобразование числа в текст:
  • Используйте функцию

    =ТЕКСТ(A2; '0')

    (TEXT).

Шаг 6: Проверьте скрытые символы и пробелы

Помимо обычных пробелов, могут существовать непечатаемые символы (например, разрывы строк, символы табуляции) или неразрывные пробелы (

CHAR(160)

), которые визуально не видны, но мешают ВПР найти совпадение.

• Функция ПРОБЕЛЫ (TRIM): Удаляет лишние пробелы в начале, конце и между словами, оставляя только один пробел между словами.
• Функция ОЧИСТИТЬ (CLEAN): Удаляет все непечатаемые символы.
• Функция ПОДСТАВИТЬ (SUBSTITUTE): Позволяет заменить определенные символы, например, неразрывные пробелы:

  =ПОДСТАВИТЬ(A2; СИМВОЛ(160); ' ')

  .

=ВПР(ПРОБЕЛЫ(ОЧИСТИТЬ(ПОДСТАВИТЬ(A2; СИМВОЛ(160); ' '))); B:C; 2; ЛОЖЬ)

Шаг 7: Проверьте объединенные ячейки

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

• Решение: По возможности, избегайте использования объединенных ячеек в данных, которые вы используете для поиска. Разъедините их.

Шаг 8: Используйте вспомогательные функции для обработки ошибок

Если вы уверены, что #Н/Д возникает из-за отсутствия значения, а не из-за ошибки в формуле, вы можете сделать вывод более ‘дружелюбным’.

• ЕСЛИОШИБКА (IFERROR): Позволяет заменить #Н/Д на любое другое значение или текст.

=ЕСЛИОШИБКА(ВПР(A2; B:C; 2; ЛОЖЬ); 'Значение не найдено')

• ИНДЕКС + ПОИСКПОЗ (INDEX + MATCH): Более гибкая и мощная альтернатива ВПР, которая не имеет ограничения на поиск только в первом столбце.

=ИНДЕКС(столбец_результата; ПОИСКПОЗ(искомое_значение; столбец_поиска; 0))