Excel lookup не работает

Исправление ошибок VLOOKUP

Ошибки Excel VLOOKUP (Содержание)

• VLOOKUP Ошибки
• # 1 — Исправление ошибки VLOOKUP # N / A
• # 2 — Исправление ошибки # VALUE в формуле VLOOKUP
• # 3- Исправление ошибки #NAME в формуле VLOOKUP
• # 4 — Исправление VLOOKUP не работает (проблемы, ограничения и решения)

VLOOKUP Ошибки

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

В этой статье мы рассмотрим простые объяснения проблем VLOOKUP, их решения и исправления.

Распространенные ошибки, когда VLOOKUP не работает:

• VLOOKUP # N / A error
• Ошибка #VALUE в формулах VLOOKUP
• Ошибка VLOOKUP #NAME
• VLOOKUP не работает (проблемы, ограничения и решения)

# 1 — Исправление ошибки VLOOKUP # N / A

Эта ошибка # N / A означает «Недоступно». Эта ошибка происходит по следующим причинам:

• Из-за ошибки в поиске аргумента значения в функции

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

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

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

Пример № 1

Давайте рассмотрим пример, чтобы понять эту проблему.

Вы можете скачать этот шаблон Excel ошибок VLOOKUP здесь — Шаблон ошибок VLOOKUP Excel

Мы дали детали продукта.

Давайте предположим, что мы хотим получить количество проданных единиц для Bitterguard.

Теперь мы применим для этого формулу VLOOKUP, как показано ниже:

И он вернет ошибку # N / A в результате.

Поскольку искомое значение «Bitterguard» появляется во втором столбце (Product) диапазона table_array A4: C13. В этом случае формула ищет значение поиска в столбце A, а не в столбце B.

Решение для VLOOKUP # N / A Ошибка

Мы можем исправить эту проблему, настроив VLOOKUP для ссылки на правильный столбец. Если это невозможно, попробуйте переместить столбцы так, чтобы столбец поиска был самым левым столбцом в таблице table_array.

# 2 — Исправление ошибки # VALUE в формуле VLOOKUP

Формула VLOOKUP отображает ошибку #VALUE, если значение, используемое в формуле, имеет неправильный тип данных. Причиной этой ошибки #VALUE может быть две причины:

Значение поиска не должно превышать 255 символов. Если он превысит этот предел, это приведет к ошибке #VALUE.

Решение для ошибки VLOOKUP #VALUE

Используя функции INDEX / MATCH вместо функции VLOOKUP, мы можем решить эту проблему.

• Правильный путь не передается как второй аргумент

Если вы хотите выбрать записи из другой книги, вам необходимо указать полный путь к этому файлу. Он будет содержать имя рабочей книги (с расширением) в квадратных скобках (), а затем указать имя листа, за которым следует восклицательный знак. Используйте апострофы вокруг всего этого, если имя книги или листа Excel содержит пробелы.

Синтаксис полной формулы для создания VLOOKUP из другой книги:

= VLOOKUP (lookup_value, ‘(имя рабочей книги) имя листа «! Table_array, col_index_num, FALSE)

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

# 3 — Исправление ошибки #NAME в формуле VLOOKUP

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

Пример № 2

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

Как мы видим, мы неправильно написали слово FALSE. Мы вводим «fa» вместо false. В результате будет возвращена ошибка #NAME.

Решение для VLOOKUP #NAME Ошибка

Проверьте правильность написания формулы, прежде чем нажать Enter.

# 4 — Исправление VLOOKUP не работает (проблемы, ограничения и решения)

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

• VLOOKUP нечувствителен к регистру

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

• Столбец был вставлен или удален из таблицы

Если вы повторно используете формулу VLOOKUP и внесли некоторые изменения в набор данных. Подобно вставленному новому столбцу или удаленному любому столбцу, это повлияет на результаты функции VLOOKUP и в этот момент не будет работать.

Всякий раз, когда вы добавляете или удаляете какой-либо столбец в наборе данных, это влияет на аргументы table_array и col_index_num.

• Копирование формулы может привести к ошибке.

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

Что нужно знать об ошибках VLOOKUP

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

Рекомендуемые статьи

Это было руководство к ошибкам VLOOKUP. Здесь мы обсудим, как исправить ошибки VLOOKUP вместе с практическими примерами и загружаемым шаблоном Excel. Вы также можете просмотреть наши другие предлагаемые статьи —

1. Руководство по функции VLOOKUP в Excel
2. Функция ISERROR Excel с примером
3. Функция IFERROR в Excel
4. Знать о наиболее распространенных ошибках Excel

Источник

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

Боретесь с функцией VLOOKUP в Microsoft Excel? Вот несколько советов по устранению неполадок, которые могут вам помочь.

Есть несколько вещей, которые выявляют пот у пользователей Microsoft Excel больше, чем мысль об ошибке 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.

Источник