Почему VLOOKUP иногда не работает

VLOOKUP надёжен, но может не сработать по нескольким предсказуемым причинам. Большинство ошибок перестают быть загадочными, если знать правила.

В этом уроке Вы узнаете распространённые причины неправильного поиска и точный способ исправить каждую из них. Эти знания помогут быстро устранять непонятные ошибки #N/A и #REF!.

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

Если идентификаторы находятся в столбце C, а нужное имя — в столбце A, VLOOKUP не сможет обратиться к нему слева. Возможны два варианта: переставить столбцы так, чтобы столбец поиска стал первым, или использовать INDEX-MATCH либо XLOOKUP, которые выполняют поиск в любом направлении.

Номер_индекса_столбца отсчитывается от левого края табличного массива, а не листа. Распространённая ошибка — использовать в качестве числа букву столбца листа.

Если Ваш диапазон — C1:F10 и Вам нужен столбец F, это 4-й столбец диапазона, поэтому индекс равен 4, а не 6. Отсчёт от неправильного края возвращает не то поле, а если число превышает ширину диапазона, возникает ошибка #REF!.

Если номер_индекса_столбца больше количества столбцов в табличном массиве, VLOOKUP возвращает #REF!.

Например, запросить столбец 5 в диапазоне из 3 столбцов A1:C10 невозможно:

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

Если не указать четвёртый аргумент, по умолчанию используется TRUE — приблизительное совпадение. В неотсортированном списке это незаметно возвращает неправильное соседнее значение вместо ошибки, поэтому проблему трудно обнаружить.

Исправление простое, и его следует превратить в привычку: всегда добавляйте FALSE для точного поиска.

Значение для поиска "A100" не совпадёт со значением "A100 " с пробелом в конце. В импортированных данных такие невидимые различия встречаются постоянно.

Признак проблемы: значение явно существует, но Вы получаете #N/A. Очистите обе стороны с помощью TRIM, чтобы удалить лишние пробелы:

Если искомое значение — это число 100, а в таблице коды хранятся как текст "100" (или наоборот), значения не совпадут, и Вы получите #N/A.

Ищите маленький зелёный треугольник или числа, выровненные по левому краю: это признаки текста. Исправьте тип данных: оберните текст в VALUE(), чтобы преобразовать его в число, или присоедините к числу пустую строку с помощью &"", чтобы преобразовать его в текст. В результате обе стороны будут иметь один и тот же тип.

Если забыть зафиксировать табличный массив, при копировании формулы вниз диапазон сместится и выйдет за пределы данных. A1:C100 во второй строке превратится в A2:C101 в третьей, а затем в A3:C102, пропуская строки по пути.

Исправьте это с помощью абсолютных ссылок: тогда таблица останется зафиксированной, а изменяться будет только искомое значение:

Каждая ошибка указывает на свою причину:

• #N/A — значение не найдено (несовпадение, пробелы, неправильный тип или действительно отсутствующее значение)
• #REF! — номер_индекса_столбца больше диапазона или указанная ячейка была удалена
• #VALUE! — аргумент имеет неправильный тип, например индекс столбца отрицателен или равен нулю
• #NAME? — имя функции написано с ошибкой, например VLOOKP

Сопоставьте ошибку с её значением — и половина проблемы уже решена.

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

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

Если поиск работает неправильно, пройдите по этому короткому списку:

• Находится ли искомое значение в первом столбце диапазона?
• Отсчитывается ли номер_индекса_столбца от левого края диапазона и не выходит ли он за его ширину?
• Добавили ли Вы FALSE для точного совпадения?
• Имеют ли обе стороны один и тот же тип (текст или число) и нет ли лишних пробелов?
• Зафиксирован ли табличный массив с помощью знаков доллара?

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

Диагностируйте этот неработающий поиск.

Основные причины и способы их устранения:

• Ограничение левого столбца — переставьте столбцы или используйте INDEX-MATCH / XLOOKUP
• Неправильный или слишком большой номер_индекса_столбца — отсчитывайте его от левого края диапазона; расширьте диапазон
• Пропущен FALSE — для идентификаторов всегда задавайте точное совпадение
• Пробелы и различие текста и чисел — очистите данные с помощью TRIM, преобразуйте их с помощью VALUE или &""
• Незафиксированная таблица — используйте $, чтобы диапазон оставался на месте

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