6 причин, почему функция ВПР не работает

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

• Вам нужно точное совпадение
• Зафиксируйте ссылки на таблицу
• Вставлен столбец
• Таблица стала больше
• ВПР не может смотреть влево
• Данные в таблице дублируются

Вам нужно точное совпадение

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

В большинстве случаев люди ищут конкретный продукт, заказ, сотрудника или клиента, и потому хотят точное совпадение. Если производится поиск уникального значения, то аргументом range_lookup (интервальный_просмотр) должно быть FALSE (ЛОЖЬ).

Этот аргумент не обязателен, но если его не указать, то будет использовано значение TRUE (ИСТИНА). В таком случае для правильной работы функции необходимо, чтобы данные были отсортированы в порядке возрастания.

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

Решение

Если Вы ищите уникальное значение, задайте последний аргумент равным FALSE (ЛОЖЬ). Функция ВПР в примере выше должна выглядеть так:

=VLOOKUP(H3,B3:F11,2,FALSE)
=ВПР(H3;B3:F11;2;ЛОЖЬ)

Зафиксируйте ссылки на таблицу

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

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

Решение

Аргумент table_array (таблица) – это таблица, которую ВПР использует для поиска и извлечения информации. Чтобы корректно скопировать функцию ВПР, в аргументе table_array (таблица) должна быть абсолютная ссылка на диапазон ячеек.

Кликните по адресу ссылки внутри формулы и нажмите F4 на клавиатуре, чтобы превратить относительную ссылку в абсолютную. Формула должна быть записана так:

=VLOOKUP($H$3,$B$3:$F$11,4,FALSE)
=ВПР(($H$3;$B$3:$F$11;4;ЛОЖЬ)

В этом примере ссылки в аргументах lookup_value (искомое_значение) и table_array (таблица) сделаны абсолютными. Иногда достаточно зафиксировать только аргумент table_array (таблица).

Вставлен столбец

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

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

Столбец Quantity (Количество) был 3-м по счету, но после добавления нового столбца он стал 4-м. Однако функция ВПР автоматически не обновилась.

Решение 1

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

Решение 2

Другой вариант – вставить функцию MATCH (ПОИСКПОЗ) в аргумент col_index_num (номер_столбца) функции ВПР.

Функция ПОИСКПОЗ может быть использована для того, чтобы найти и возвратить номер требуемого столбца. Это сделает аргумент col_index_num (номер_столбца) динамичным, т.е. можно будет вставлять новые столбцы в таблицу, не влияя на работу функции ВПР.

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

=VLOOKUP(I3,B3:G11,MATCH(J2,B2:G2,0),FALSE)
=ВПР(I3;B3:G11;ПОИСКПОЗ(J2;B2:G2;0);ЛОЖЬ)

Таблица стала больше

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

Решение

Форматируйте диапазон ячеек как таблицу (Excel 2007+) или как именованный диапазон. Такие приёмы дадут гарантию, что ВПР всегда будет обрабатывать всю таблицу.

Чтобы форматировать диапазон как таблицу, выделите диапазон ячеек, который собираетесь использовать для аргумента table_array (таблица). На Ленте меню нажмите Home > Format as Table (Главная > Форматировать как таблицу) и выберите стиль из галереи. Откройте вкладку Table Tools > Design (Работа с таблицами > Конструктор) и в соответствующем поле измените имя таблицы.

В формуле на рисунке ниже использовано имя таблицы FruitList.

ВПР не может смотреть влево

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

Решение

Решение этой проблемы – не использовать ВПР вовсе. Используйте комбинацию функций INDEX (ИНДЕКС) и MATCH (ПОИСКПОЗ), которая стала привычной альтернативой для ВПР. Это намного более гибкое решение

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

=INDEX(B3:B13,MATCH(H3,C3:C13,0))
=ИНДЕКС(B3:B13;ПОИСКПОЗ(H3;C3:C13;0))

Данные в таблице дублируются

Функция ВПР может извлечь только одну запись. Она возвратит первую найденную запись, соответствующую введённому Вами условию поиска.

Если таблица содержит повторяющиеся значения, функция ВПР не справится с такой задачей правильно.

Решение 1

Нужны ли Вам повторяющиеся данные в списке? Если нет – удалите их. Это можно сделать быстро при помощи кнопки Removes Duplicates (Удалить дубликаты) на вкладке Data (Данные).

Решение 2

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

Таблица ниже – это список заказов. Допустим, Вы хотите найти все заказы определённого фрукта.

Сводная таблица позволяет выбрать значение из столбца ID в фильтре, которое соответствует определенному фрукту, и получить список всех связанных заказов. В нашем примере выбрано значение ID равное 23 (Бананы).

ВПР без забот

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