XLOOKUP

XLOOKUP — это функция электронных таблиц, предназначенная для поиска значения в заданном диапазоне или массиве данных и возврата соответствующего значения из другого диапазона или массива. Относится к категории поисковых и справочных функций. Была впервые представлена корпорацией Microsoft в марте 2019 года для приложений Microsoft 365, а впоследствии стала доступна в Excel 2021 и более поздних версиях. XLOOKUP рассматривается как более современная и гибкая замена более старым функциям ВПР (VLOOKUP), ГПР (HLOOKUP) и ПОИСКПОЗ (INDEX/MATCH).

История

Функция XLOOKUP была анонсирована в августе 2019 года как часть обновления для подписчиков Microsoft 365. Её появление стало ответом на многолетние запросы пользователей, критиковавших ограничения функции ВПР: невозможность поиска слева направо (искомый столбец должен был быть крайним левым в таблице), фиксированное количество столбцов при вставке новых данных, а также сложность обработки ошибок. Разработчики Microsoft позиционировали XLOOKUP как «убийцу ВПР», способную выполнять большинство задач, для которых ранее требовались комбинации нескольких функций.

Первоначально функция стала доступна только в бета-версиях для участников программы Office Insiders. К концу 2019 года она была развёрнута для всех пользователей Office 365. В 2021 году, с выходом версии Excel 2021, XLOOKUP стала частью стандартной поставки для всех новых пользователей, не имеющих подписки. Некоторые сторонние табличные процессоры, такие как LibreOffice Calc и Google Sheets, также внедрили аналогичные функции с похожим синтаксисом, хотя точная реализация может отличаться.

Синтаксис и аргументы

Функция XLOOKUP имеет следующий синтаксис:

=XLOOKUP(искомое_значение; массив_просмотра; массив_возврата; [значение_если_не_найдено]; [режим_сопоставления]; [режим_поиска])

Описание аргументов:

• искомое_значение (обязательный) — значение, которое требуется найти. Может быть числом, текстом, датой или ссылкой на ячейку.
• массив_просмотра (обязательный) — диапазон или массив, в котором производится поиск. Может быть как одним столбцом, так и одной строкой.
• массив_возврата (обязательный) — диапазон или массив, из которого возвращается результат. Размер этого массива должен совпадать с размером массива просмотра.
• значение_если_не_найдено (необязательный) — значение, которое возвращается, если искомое значение не найдено. Если этот аргумент опущен, по умолчанию возвращается ошибка #Н/Д. Позволяет выводить произвольный текст, например «Не найдено».
• режим_сопоставления (необязательный) — определяет тип сопоставления:
• 0 — точное совпадение (по умолчанию). Если совпадений несколько, возвращается первое найденное.
• -1 — точное совпадение или следующее меньшее значение (массив просмотра должен быть отсортирован по убыванию).
• 1 — точное совпадение или следующее большее значение (массив просмотра должен быть отсортирован по возрастанию).
• 2 — сопоставление по подстановочному знаку (символы *, ?, ~).
• режим_поиска (необязательный) — задаёт направление поиска:
• 1 — поиск от первого элемента к последнему (по умолчанию).
• -1 — поиск от последнего элемента к первому (обратный поиск).
• 2 — бинарный поиск по массиву, отсортированному по возрастанию.
• -2 — бинарный поиск по массиву, отсортированному по убыванию.

Возможности и преимущества

XLOOKUP обладает рядом существенных преимуществ по сравнению с предшественниками:

• Поиск в любом направлении. В отличие от ВПР, которая ищет только в крайнем левом столбце и возвращает значение из столбца справа, XLOOKUP может искать в любом столбце или строке и возвращать значение из любого другого столбца или строки слева или справа от массива просмотра.
• Возврат целого массива. Функция может возвращать не одно значение, а целый диапазон значений. Например, используя XLOOKUP с одним искомым значением и указав в качестве массива возврата несколько столбцов, можно получить сразу все данные по найденной записи (аналог комбинации INDEX и MATCH).
• Встроенная обработка ошибок. Аргумент «значение_если_не_найдено» избавляет от необходимости использовать отдельную функцию ЕСЛИОШИБКА для обработки ситуаций, когда искомое значение отсутствует в данных.
• Гибкость в сопоставлении. Четыре режима сопоставления покрывают практически все сценарии: точное совпадение, приблизительное совпадение (как большее, так и меньшее) и поиск по шаблону.
• Управление направлением поиска. Возможность поиска с конца массива (режим -1) позволяет легко находить последнее вхождение значения, что было сложно реализовать с помощью ВПР.
• Независимость от вставки столбцов. Поскольку XLOOKUP использует отдельные ссылки на массивы просмотра и возврата, вставка новых столбцов в исходную таблицу не нарушает работу формулы, в отличие от ВПР, где номер столбца задаётся числом.

Примеры использования

Простой поиск по точному совпадению

Допустим, в таблице A1:B10 содержатся коды товаров (столбец A) и их цены (столбец B). Требуется найти цену товара с кодом «Т-101»:

=XLOOKUP("Т-101"; A1:A10; B1:B10)

Поиск с обработкой отсутствия значения

=XLOOKUP("Т-999"; A1:A10; B1:B10; "Товар не найден")

Если код «Т-999» отсутствует, в ячейке отобразится текст «Товар не найден».

Поиск последнего вхождения

В таблице продаж (A2:A100 — даты, B2:B100 — суммы) требуется найти сумму последней продажи за определённую дату:

=XLOOKUP(D1; A2:A100; B2:B100; 0; 0; -1)

Здесь D1 — ячейка с искомой датой, а режим поиска -1 заставляет функцию просматривать массив с конца.

Возврат нескольких значений

Имеется таблица сотрудников: A2:A100 — ID, B2:B100 — ФИО, C2:C100 — Отдел, D2:D100 — Оклад. Чтобы по ID (в ячейке F1) получить ФИО, Отдел и Оклад, можно использовать одну формулу:

=XLOOKUP(F1; A2:A100; B2:D100)

Функция вернёт массив из трёх значений, который при вводе в три смежные ячейки (как формула массива) заполнит их соответствующими данными.

Критика и ограничения

Несмотря на широкое признание, XLOOKUP не лишена недостатков:

• Зависимость от версии. Функция не работает в старых версиях Excel (2016, 2013, 2010 и более ранних), что ограничивает её использование в средах с устаревшим программным обеспечением. В таких случаях по-прежнему приходится применять ВПР или комбинацию ИНДЕКС+ПОИСКПОЗ.
• Отсутствие обратной совместимости. Файлы, содержащие XLOOKUP, при открытии в старой версии Excel отображают ошибку #ИМЯ?, что требует от пользователей осознанного выбора между новыми возможностями и совместимостью.
• Сложность для новичков. Хотя синтаксис проще, чем у комбинации ИНДЕКС+ПОИСКПОЗ, наличие трёх необязательных аргументов может отпугнуть начинающих пользователей, которым достаточно простого точного совпадения.
• Производительность. На очень больших наборах данных (сотни тысяч строк) XLOOKUP может работать медленнее, чем оптимизированные версии ВПР или ИНДЕКС+ПОИСКПОЗ, особенно при использовании режимов приблизительного сопоставления или поиска с конца.

Сравнение с другими функциями

Применение

XLOOKUP широко применяется в задачах анализа данных, финансового моделирования, управления базами данных, а также в образовательных и административных целях. Она используется для объединения таблиц по ключевому полю (аналог VLOOKUP), для поиска справочных значений, для динамического построения отчётов и дашбордов. Функция интегрирована во все современные версии Excel для Windows, macOS, веб-версию, а также в мобильные приложения Microsoft 365.

Источники

• Справка Microsoft: «Функция XLOOKUP» (Microsoft Support)
• Документация Excel 2021 и Microsoft 365
• Учебные материалы по работе с электронными таблицами