Обработка отсутствующих совпадений с помощью if_not_found

Что происходит, когда XLOOKUP не может найти нужное значение? По умолчанию функция возвращает ошибку #N/A.

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

У XLOOKUP есть необязательный четвёртый аргумент под названием if_not_found. Если совпадение не найдено, возвращается указанное в нём значение.

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

Самый распространённый вариант — показывать понятный текст вместо #N/A.

Если товар из D2 отсутствует в списке, в ячейке отображается «Не найдено», а не код ошибки. Любой, кто читает лист, сразу понимает, что произошло.

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

Если совпадение не найдено, можно вернуть 0, чтобы последующие SUM или умножение продолжали работать и не создавали собственную ошибку.

Чтобы визуально оставить ячейку пустой, когда совпадение не найдено, верните пустую текстовую строку, используя две кавычки.

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

Предположим, менеджер по продажам вводит ID заказа в D2, чтобы найти имя клиента. ID заказов находятся в столбце A, а имена — в столбце C.

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

Значение «если не найдено» не обязательно вводить вручную. Оно может ссылаться на другую ячейку.

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

Вы по-прежнему можете обернуть XLOOKUP в IFERROR, но между этими вариантами есть важное различие.

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

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

Полезный приём: сделать резервным вариантом одного XLOOKUP другой XLOOKUP.

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

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

Здесь для отсутствующей цены возвращается 0, поэтому умножение на количество в E2 по-прежнему даёт число, а не распространяющуюся по листу ошибку.

Выбирайте резервное значение в соответствии с назначением ячейки:

• Текст вроде «Не найдено» — для понятных пользователю отчётов
• 0 — если значение позже складывается или умножается
• Пустая строка — для аккуратного визуального оформления
• Другой поиск — для данных из нескольких источников

Четвёртый аргумент превращает ненадёжные поисковые формулы в аккуратный профессиональный результат.

Проверьте, насколько Вы поняли аргумент «если не найдено».

Вы научились обрабатывать случаи, когда поиск ничего не находит:

• XLOOKUP по умолчанию возвращает #N/A, если совпадение отсутствует
• Необязательный аргумент если не найдено задаёт аккуратное резервное значение
• Можно использовать текст, 0, пустое значение, ячейку или даже второй XLOOKUP
• Он обрабатывает только отсутствие совпадения, поэтому настоящие ошибки остаются видимыми

Далее Вы научитесь выполнять поиск слева и снизу списка.