Как использовать XLOOKUP с несколькими критериями в Excel

Быстрые ссылки

• Синтаксис XLOOKUP и пример с одним критерием
• Пример 1: несколько критериев через булеву логику
• Пример 2: несколько критериев через конкатенацию
• Альтернативы и рекомендации для производительности

Введение и совместимость

XLOOKUP — современная функция поиска в Excel, замена VLOOKUP/HLOOKUP и гибрид INDEX+MATCH. Простой миф: XLOOKUP принимает только одно условие. На практике функция легко обрабатывает несколько критериев двумя общеупотребимыми способами: через булевы массивы или через конкатенацию строк.

Требования по версии: чтобы использовать XLOOKUP и расширенные приёмы с динамическими массивами, у вас должна быть версия Excel 2021, Microsoft 365, Excel для веба или мобильные/планшетные приложения. Некоторые дополнительные приёмы (LET, FILTER) тоже требуют современных сборок Excel.

Определение в одну строку: XLOOKUP — функция поиска, возвращающая значение из «массива возврата» по совпадению в «массиве поиска»; поддерживает режимы совпадения и направление поиска.

Важно: в некоторых локалях (например, в русской локали Excel) формулы используют точку с запятой (;) вместо запятой (,) в качестве разделителя аргументов. Если формула не работает — попробуйте заменить запятые на точку с запятой.

Синтаксис XLOOKUP и пример одного критерия

Синтаксис выглядит так:

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

Кратко о аргументах:

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

Пример: после выбора ID в выпадающем списке в ячейке E1 формула

=XLOOKUP(E1,T_Scores[ID],T_Scores[Score],"No match",0,1)

в ячейке E2 возвращает значение 51 (пример из таблицы). Это стандартный точный поиск сверху вниз.

Когда нужен поиск по нескольким критериям

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

XLOOKUP по умолчанию работает с одним lookup_value. Но можно сформировать временный массив (lookup_array) на основе нескольких условий — и затем найти первое совпадение.

Пример 1: несколько критериев через булеву логику (AND / OR)

Идея: создать для каждой строки таблицы логические маски по критериям, перевести TRUE/FALSE в 1/0 и умножением получить позицию, где все условия одновременно выполняются.

Формула (введена в одну строку, здесь разбита для читабельности):

=XLOOKUP(
  1,
  (T_Managers[Drink]=G1)*(T_Managers[Country]=G2),
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Логика работы:

1. (T_Managers[Drink]=G1) возвращает временный массив TRUE/FALSE по соответствию напитка.
2. (T_Managers[Country]=G2) возвращает массив TRUE/FALSE по стране.
3. Умножение (*) превращает TRUE в 1 и FALSE в 0 и оставляет 1 только там, где оба условия истинны.
4. XLOOKUP ищет число 1 в полученном массиве. Первая позиция с 1 — это строка с нужной комбинацией.
5. Из return_array возвращаются соответствующие значения Manager и Sales.

Подробная иллюстрация промежуточных массивов (примерные результаты):

• Проверка по напитку (Coffee) дает массив вида {FALSE;FALSE;…;TRUE;TRUE;…}
• Проверка по стране (Spain) дает массив вида {FALSE;…;TRUE;…;TRUE;…}
• При умножении получаем {0;0;…;1;0;…} — единица там, где оба условия совпали.

В примере XLOOKUP находит первую единицу и возвращает из соответствующих столбцов Manager = Olivia и Sales = 346.

Советы и расширения:

• Для логического OR используйте сложение (+) вместо умножения: (A=val1)+(B=val2) — вернёт 1, если хотя бы одно условие истинно.
• Операторы сравнения (> , < , >= , <=) также работают: (T_Table[Qty]>G1) создаёт логический массив по порогу.
• Если вы возвращаете несколько столбцов (как в примере Manager и Sales), XLOOKUP вернёт динамический массив (в поддерживающих версиях Excel).

Пример 2: несколько критериев через конкатенацию строк (&)

Идея: создать «ключ» — объединить значения критериев в одну строку и сделать то же самое для строк таблицы.

Формула:

=XLOOKUP(
  G1&G2,
  T_Managers[Drink]&T_Managers[Country],
  T_Managers[[Manager]:[Sales]],
  "No result"
)

Здесь lookup_value = “CoffeeSpain”, а lookup_array — массив строк типа “Apple juiceAustralia”, “CoffeeSpain” и т.д. XLOOKUP ищет точную строку и возвращает соответствующие колонки.

Преимущества и ограничения конкатенации:

• Проще и читаемее в большинстве простых случаев.
• Подходит, если комбинации уникальны и нет нюансов с пробелами/форматами.
• Чувствительна к пробелам и регистру (если нужно нечувствительное сравнение, используйте UPPER/LOWER вокруг частей).
• В больших таблицах конкатенация создаёт временные строки для каждой строки и может быть медленнее.

Сравнение методов — когда что выбрать

• Простота: конкатенация выигрывает — формула короче и понятнее.
• Гибкость: булева логика лучше, если нужны сравнения (>, <), частичные совпадения или смешанные операторы.
• Производительность: на очень больших наборах данных FILTER+INDEX или Power Query могут работать быстрее и прозрачно.
• Читайте локальные настройки Excel: разделители аргументов и формат дат могут менять вид формул.

Альтернативные подходы

1. FILTER + INDEX

Если нужна вся строка или несколько совпадающих строк:

=FILTER(T_Managers, (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), "No result")

FILTER вернёт все строки, соответствующие критериям; затем можно взять первую строку с INDEX или работать с массивом дальше.

2. INDEX + MATCH с переносом на массивы

=INDEX(T_Managers[[Manager]:[Sales]], MATCH(1, (T_Managers[Drink]=G1)*(T_Managers[Country]=G2), 0), 0)

Этот подход был стандартом до появления XLOOKUP.

3. SUMIFS / COUNTIFS для агрегатов

Если нужно суммировать или подсчитать по нескольким критериям, используйте SUMIFS или COUNTIFS — это быстрее и выразительнее для агрегатов.

4. Power Query

Для массовой подготовки данных, объединения, группировки и последующего анализа Power Query часто более устойчив и масштабируем.

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

1. Подтвердите версию Excel (XLOOKUP/LET/FILTER доступны в 2021/365).
2. Очистите данные: уберите лишние пробелы, проверьте регистр и типы (текст/число).
3. Решите: нужен ли один точный результат или несколько совпадений.
4. Выберите метод: конкатенация (простые случаи) или булева логика (сложные критерии).
5. Постройте формулу и протестируйте на краевых строках (первая/последняя/отсутствующая комбинации).
6. Добавьте if_not_found, чтобы избежать #N/A.
7. При необходимости используйте LET для читаемости и повторного использования вычислений.

Пример с LET (повышает читаемость):

=LET(
  drink, G1,
  country, G2,
  key, drink&country,
  arr, T_Managers[Drink]&T_Managers[Country],
  XLOOKUP(key, arr, T_Managers[[Manager]:[Sales]], "No result")
)

Контрольные тесты и критерии приёмки

• Тест 1: Существующая комбинация (например, Coffee + Spain) возвращает корректный Manager и Sales.
• Тест 2: Несуществующая комбинация возвращает текст из if_not_found.
• Тест 3: Для числовых критериев проверки > / < работать корректно при использовании булевой логики.
• Тест 4: Формула остаётся стабильной при добавлении/удалении строк таблицы (используйте структурированные ссылки на таблицы Excel).

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

• Формула возвращает ожидаемые значения для всех тестовых случаев.
• Формула не даёт ошибок #N/A или #VALUE в рамках предусмотренных ситуаций.
• Формула выполнена с учётом локали (разделители аргументов и форматы).

Производительность и масштабирование

• Малые и средние таблицы: оба метода работают быстро.
• Большие таблицы (десятки тысяч строк): булевы массивы и конкатенация могут замедлить расчёты, особенно в старых версиях Excel.
• Рекомендуется использовать структурированные таблицы (Ctrl+T), чтобы добавление строк автоматически учитывалось.
• Для больших наборов рассмотрите Power Query как ETL: подготовьте ключи и затем используйте простые VLOOKUP/XLOOKUP по готовому ключу.

Контрпримеры и типичные ошибки

• Ошибка разделителей: в русской локали нужно использовать точку с запятой: =XLOOKUP(A1;B:B;C:C)
• Проблемы с пробелами: “Spain” и “ Spain” — разные строки; использовать TRIM при подготовке данных.
• Чувствительность к регистру: XLOOKUP чувствителен к регистру при сравнении строк; для нечувствительного сравнения оборачивайте части в UPPER/LOWER.
• Несовпадение типов: сравнение числа и текста вернёт FALSE; приводите типы явно.

Шаблоны и сниппеты (cheat sheet)

1. AND через умножение, возвращает первую строку:

=XLOOKUP(1,(Range1=Value1)*(Range2=Value2),ReturnRange,"No result")

2. OR через сложение, возвращает первую строку, где хотя бы одно условие истинно:

=XLOOKUP(1,(Range1=Value1)+(Range2=Value2),ReturnRange,"No result")

3. Конкатенация ключей:

=XLOOKUP(A1&B1, Table[ColA]&Table[ColB], Table[[Result1]:[Result2]], "No result")

4. LET для читаемости:

=LET(k, A1&B1, arr, Table[ColA]&Table[ColB], XLOOKUP(k, arr, Table[[Res1]:[Res2]], "No result"))

Практические рекомендации по локализации (для русскоязычных пользователей)

• Разделитель аргументов: проверьте системные региональные настройки и локаль Excel; используйте точку с запятой (;) если требуется.
• Формат дат: сравнивайте даты в виде чисел (DATEVALUE/DATE) или приводите к единому формату.
• Десятичный разделитель: при работе с числовыми критериями учитывайте запятую и точку.

Роль‑ориентированные чеклисты

Аналитик:

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

Бухгалтер:

• Использует SUMIFS для агрегатов, а XLOOKUP — для подтягивания справочных значений.
• Убедился в корректности форматов чисел.

BI‑специалист:

• Для больших объёмов предпочитает Power Query или модели Power BI.
• Подготовил ключи и индексы для ускоренного поиска.

Небольшая методология принятия решения (мини‑flow)

• Нужно одно точное значение? — Используйте XLOOKUP (конкатенация если несколько полей).
• Нужно вернуть несколько строк? — Используйте FILTER.
• Нужна агрегация? — SUMIFS/COUNTIFS.
• Данные большие/подготовка сложная? — Power Query.

flowchart TD
  A[Нужен поиск по нескольким критериям?] -->|Нет| B[XLOOKUP простой]
  A -->|Да| C{Результат один или несколько}
  C -->|Один| D{Комбинации уникальны?}
  D -->|Да| E[Конкатенация &]
  D -->|Нет| F[Булева логика * / +]
  C -->|Несколько| G[FILTER или Power Query]

Резюме

XLOOKUP — мощный инструмент для поиска по одному и нескольким критериям. Для простых и уникальных ключей удобна конкатенация (&). Для сложных условий и смешанных операторов (> , < , OR/AND) гибче булева логика через умножение/сложение временных массивов. Для больших наборов или массовой подготовки данных рассмотрите FILTER, Power Query или агрегирующие функции.

Важно: учитывайте локаль Excel (разделители аргументов, формат дат и чисел) и используйте LET/структурированные таблицы для читаемости и надежности.

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