XLOOKUP в Excel — руководство и подробные примеры

Что такое XLOOKUP

XLOOKUP — это универсальная функция поиска в Microsoft Excel. Она ищет значение в заданном диапазоне (строке или столбце) и возвращает связанное значение из другого диапазона. В отличие от устаревшего VLOOKUP, XLOOKUP не зависит от положения возвращаемого столбца и проще настраивается для точных и приближённых соответствий.

Определение: XLOOKUP ищет значение и возвращает соответствующее значение из отдельного диапазона. Это упрощённая замена паре функций VLOOKUP/HLOOKUP или INDEX/MATCH.

Доступность и совместимость

Important: XLOOKUP доступен только в Microsoft 365 (Office 365) и Office Online. Пользователи Office 2010–2019 не увидят эту функцию. Если вы обмениваетесь файлами с пользователями старых версий, подготовьте запасные формулы или описанные ниже обходы.

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

Синтаксис XLOOKUP

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Параметры:

• lookup_value — значение для поиска.
• lookup_array — диапазон, в котором ищем (одноизмерный: строка или столбец).
• return_array — диапазон, из которого возвращаем результат. Длина должна соответствовать lookup_array.
• [if_not_found] — необязательное значение, возвращаемое при отсутствии совпадения (например, “Не найдено”).
• [match_mode] — режим сопоставления:
  • 0 (по умолчанию) — точное соответствие.
  • -1 — точное или ближайшее меньшее значение.
  • 1 — точное или ближайшее большее значение.
  • 2 — побуквенное/масочное соответствие с подстановочными символами (*, ?, ~).
• [search_mode] — режим поиска в lookup_array:
  • 1 (по умолчанию) — поиск сверху вниз / слева направо.
  • -1 — обратный поиск (с конца).
  • 2 — бинарный поиск по отсортированным по возрастанию данным.
  • -2 — бинарный поиск по отсортированным по убыванию данным.

Пример базовой формы: =XLOOKUP(A2, B2:B100, C2:C100, "—").

Быстрые факты

• Максимальное число аргументов: 6.
• Значение по умолчанию для match_mode: 0 (точное).
• Значение по умолчанию для search_mode: 1 (обычный поиск).
• XLOOKUP может возвращать несколько столбцов (динамический массив).

Преимущества и недостатки XLOOKUP

Преимущества

• Работает по вертикали и горизонтали.
• Не требует объединения столбцов: lookup_array и return_array могут быть в любой части листа.
• По умолчанию ищет точное совпадение, что снижает неожиданные результаты.
• Поддерживает подстановочные символы для частичных совпадений.
• Может возвращать несколько столбцов/строк (поведение spill).
• Упрощает замену комбинации INDEX + MATCH одним выражением.

Недостатки

• Доступна только в Microsoft 365 / Office Online.
• Требует одинаковой длины lookup_array и return_array — иначе появится ошибка.
• Для начинающих опциональные аргументы могут показаться сложными.
• При выборе двух длинных диапазонов вручную функция может замедлить работу на больших листах.

Как начать: методика для быстрого внедрения

Мини-методология для корректного использования XLOOKUP в рабочем файле:

1. Проверка совместимости: убедитесь, что пользователи имеют Microsoft 365.
2. Определите lookup_value и убедитесь, что lookup_array — одномерный диапазон (столбец или строка).
3. Убедитесь, что return_array совпадает по длине с lookup_array.
4. Задайте [if_not_found] для дружественной обработки ошибок.
5. Выберите match_mode и search_mode в зависимости от задачи.
6. Тестируйте на граничных значениях и пустых ячейках.

Примеры: пошаговые сценарии

Ниже — расширенные, пояснённые примеры по оригинальному материалу.

Пример 1: базовый точный поиск

Дано: таблица с именами в столбце A и предметами/оценками в столбцах B–D. В ячейке F2 содержится имя — Matthew. Задача: получить оценку по предмету “Science”.

Формула в G2 (пример):

=XLOOKUP(F2, A2:A15, C2:C15, "Не найдено")

Пояснение: ищем значение в столбце A и возвращаем значение из столбца C. Если имя отсутствует, будет показано “Не найдено”.

Пример 2: lookup_array справа от return_array

Если список имён находится справа, а оценки слева, XLOOKUP всё равно работает. Предположим, имена в D2:D15, оценки в A2:A15. Формула:

=XLOOKUP(F2, D2:D15, A2:A15, "Не найдено")

Пояснение: порядок столбцов не имеет значения. VLOOKUP бы потребовал перестановки или индексного номера столбца.

Пример 3: возврат нескольких значений одновременно

XLOOKUP может возвращать несколько столбцов — полезно для получения оценок по всем предметам сразу. Если A2:C15 содержит три столбца с предметами, формула:

=XLOOKUP(F2, D2:D15, A2:C15, "Не найдено")

Результат будет “растекаться” в соседние ячейки (spill). Убедитесь, что справа нет занятых ячеек.

Пример 4: использование if_not_found и частичного совпадения

Формула с обработкой отсутствия значения и подстановочным символом:

=XLOOKUP("Matt*", A2:A15, C2:C15, "Не найдено", 2)

Здесь match_mode = 2 позволяет использовать * и ?. Это полезно, если в таблице записаны сокращённые или вариативные имена.

Пример 5: обратный поиск и ускорённый поиск

Обратный поиск, чтобы найти последнее вхождение имени в списке:

=XLOOKUP(F2, A2:A1000, C2:C1000, "Не найдено", 0, -1)

Для больших отсортированных наборов можно использовать бинарный поиск (search_mode = 2 или -2) — это быстрее, но требует строгой сортировки:

=XLOOKUP(12345, A2:A100000, B2:B100000, "Не найдено", 1, 2)

Important: бинарный поиск работает только на отсортированных данных. Несортированный массив даст неверный результат.

Ошибки и способы их исправления

• #N/A — нет совпадений. Используйте аргумент if_not_found, или оберните XLOOKUP в IFERROR для совместимости.
• #REF! — возвращаемый массив не умещается, либо несоответствие длины lookup_array и return_array.
• #VALUE! — неверный тип аргумента или диапазоны разной ориентации.
• Неверные результаты при использовании бинарного поиска — проверьте сортировку.

Советы по отладке:

• Всегда проверяйте, что длина lookup_array = длине return_array.
• Если возвращается массив (spill), убедитесь, что справа свободно.
• Для текстовых совпадений уберите лишние пробелы функцией TRIM или используйте CLEAN.

Сравнение XLOOKUP, VLOOKUP и INDEX/MATCH

Вывод: XLOOKUP упрощает большинство сценариев, где ранее применялись VLOOKUP или INDEX+MATCH.

Когда XLOOKUP не подходит

• Получатели используют Excel 2019 или старее без Office 365 — функция будет недоступна.
• Нужна обратная совместимость на уровне формул в файлах, которые должны работать в старых версиях.
• Требуется компактный файл для очень старых сценариев автоматизации, где макросы и старые зависимости — единственный вариант.

В таких случаях рассмотрите использование VLOOKUP с IFERROR или заранее вычисляйте и фиксируйте результаты (Paste Values) перед отправкой.

Практические рекомендации и эвристики

• Всегда задавайте [if_not_found] — это делает книгу более дружелюбной.
• Для поиска “последнего вхождения” используйте search_mode = -1.
• При работе с большими наборами данных оцените возможность бинарного поиска, но убедитесь в сортировке.
• Для масочного поиска используйте match_mode = 2 и экранируйте символ ~ при необходимости.
• Если нужны результаты из нескольких таблиц — используйте XLOOKUP внутри LET/WRAP для читаемости.

Чек-листы по ролям

Аналитик:

• Проверить, что lookup_array и return_array выровнены по длине.
• Добавить дружественное сообщение для отсутствующих значений.
• Протестировать на манекенах и на реальном наборе данных.

Разработчик отчётов:

• Убедиться, что результаты XLOOKUP не ломают вычисления дальше по цепочке.
• Зафиксировать (Paste Values) тяжёлые вычисления перед рассылкой.

Менеджер проекта:

• Согласовать минимальную версию Excel среди получателей.
• Поддержать внедрение Microsoft 365 при необходимости.

Критерии приёмки

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

Тесты и примеры приёмки

1. Тест с существующим именем — должен вернуть корректную оценку.
2. Тест с отсутствующим именем — должен вернуть if_not_found.
3. Тест на обратный поиск — при дублирующихся значениях должен вернуть последнее вхождение.
4. Тест бинарного поиска — проверить на отсортированном и несортированном наборе; несортированный должен дать предупреждение.

Миграция и совместная работа

Если файл должны открыть пользователи старых версий Excel:

• Вариант 1: заменить формулы XLOOKUP на значения (Paste Values) перед отправкой.
• Вариант 2: добавить запасной столбец с VLOOKUP/INDEX+MATCH и пометить его как совместимый.
• Вариант 3: использовать Office Online при совместной работе — XLOOKUP доступен там.

Совет: добавьте пояснительную вкладку с описанием используемых функций и их требований к версии Excel.

Глоссарий (одна строка)

• lookup_value — значение, которое ищем; lookup_array — область поиска; return_array — область возврата; spill — поведение динамического массива при заполнении соседних ячеек.

Сводка

XLOOKUP — современная и удобная функция поиска в Excel, которая упрощает многие сценарии, где ранее использовались VLOOKUP или INDEX+MATCH. Она гибка, поддерживает точные и приближённые совпадения, умеет возвращать множество столбцов и обеспечивает более понятное поведение по умолчанию. Главный практический недостаток — доступность только в Microsoft 365 и Office Online. Планируйте распространение файлов и добавляйте запасные варианты для пользователей старых версий.

Заметка: если вы ещё не используете Microsoft 365, рассмотрите обновление по мере необходимости или храните результаты поиска как значения для совместимости.

Ключевые действия после прочтения:

• Попробуйте XLOOKUP на небольшом наборе данных.
• Добавьте обработку отсутствующих значений через [if_not_found].
• Подумайте о совместимости при рассылке файлов.