Как устранить ошибки VLOOKUP в Excel

Как

ПоБен Стоктон

Последнее обновление13 января 2020 г.

Есть несколько вещей, которые выделяют пот в Майкрософт Эксель пользователи больше, чем мысли об ошибке VLOOKUP. Если вы не слишком знакомы с Excel, VLOOKUP является одной из самых сложных или, по крайней мере, одной из наименее понятных доступных функций.

Целью VLOOKUP является поиск и возврат данных из другого столбца в вашей электронной таблице Excel. К сожалению, если вы неправильно укажете формулу VLOOKUP, Excel выдаст вам ошибку. Давайте рассмотрим некоторые распространенные ошибки VLOOKUP и объясним, как их устранять.

Ограничения VLOOKUP

Прежде чем начать использовать VLOOKUP, вы должны знать, что это не всегда лучший вариант для пользователей Excel.

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

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

Функции INDEX и MATCH могли бы стать хорошей альтернативой, если возникнет проблема с любой из этих проблем, как и новая функция XLOOKUP в Excel, которая в настоящее время находится в стадии бета-тестирования.

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

Есть некоторые дополнительные ограничения для формул VLOOKUP, которые могут вызвать ошибки, как мы объясним далее.

Ошибки VLOOKUP и # N / A

Одной из самых распространенных ошибок VLOOKUP в Excel является # N / A ошибка. Эта ошибка возникает, когда VLOOKUP не может найти искомое значение.

Начнем с того, что значение поиска может отсутствовать в вашем диапазоне данных, или вы, возможно, использовали неправильное значение. Если вы видите ошибку N / A, дважды проверьте значение в формуле VLOOKUP.

Если значение верное, то вашего поискового значения не существует. Это предполагает, что вы используете VLOOKUP для поиска точных совпадений, с range_lookup аргумент установлен в ЛОЖНЫЙ.

В приведенном выше примере, поиск Студенческий билет с номер 104 (в клетке G4) возвращает Ошибка # N / A потому что минимальный идентификационный номер в диапазоне 105.

Если range_lookup аргумент в конце формулы VLOOKUP отсутствует или установлен в ПРАВДАтогда VLOOKUP вернет ошибку # N / A, если ваш диапазон данных не отсортирован в порядке возрастания.

Он также вернет ошибку # N / A, если значение вашего поиска меньше, чем самое низкое значение в диапазоне.

В приведенном выше примере Студенческий билет значения перепутаны. Несмотря на ценность 105 VLOOKUP не может выполнить правильный поиск с помощью range_lookup аргумент установлен в ПРАВДА так как колонка А не отсортировано по возрастанию

Другие распространенные причины ошибок # N / A включают использование столбца поиска, который не является самым дальним слева, и использование ячейки ссылки для значений поиска, которые содержат числа, но отформатированы как текст или содержат лишние символы, такие как пространства.

Устранение ошибок #VALUE

#ЗНАЧЕНИЕ ошибка обычно является признаком того, что формула, содержащая функцию VLOOKUP, в некотором роде неверна.

В большинстве случаев это обычно происходит из-за ячейки, на которую вы ссылаетесь в качестве значения поиска. максимальный размер значения поиска VLOOKUP 255 символов.

Если вы имеете дело с ячейками, которые содержат более длинные строки символов, VLOOKUP не сможет их обработать.

Единственный обходной путь для этого - заменить формулу VLOOKUP комбинированной формулой INDEX и MATCH. Например, если в одном столбце содержится ячейка со строкой из более чем 255 символов, для поиска этих данных можно использовать вложенную функцию MATCH внутри INDEX.

В приведенном ниже примере ПОКАЗАТЕЛЬ возвращает значение в ячейке B4используя диапазон в колонка А определить правильный ряд. Это использует вложенный СОВПАДЕНИЕ функция для идентификации строки в колонка А (содержит 300 символов), который соответствует ячейке H4.

В данном случае это клетка A4, с ПОКАЗАТЕЛЬ возврате 108 (значение B4).

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

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

Если вы столкнулись с ошибкой #VALUE, дважды проверьте формулу VLOOKUP, чтобы убедиться, что ссылки на ваши ячейки верны.

#NAME и VLOOKUP

Если ваша ошибка VLOOKUP не является ошибкой #VALUE или ошибкой # N / A, то это, вероятно, #ИМЯ ошибка. Прежде чем паниковать при мысли об этом, будьте уверены - это самая простая ошибка VLOOKUP, которую нужно исправить.

Ошибка #NAME появляется, когда вы ошиблись в функции в Excel, будь то VLOOKUP или другая функция, такая как SUM. Нажмите на ячейку VLOOKUP и проверьте, правильно ли вы написали VLOOKUP.

Если других проблем нет, ваша формула VLOOKUP будет работать после исправления этой ошибки.

Использование других функций Excel

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

Если VLOOKUP не подходит для вас, воспользуйтесь этими лучшие функции Excel вместо.