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

Как можно использовать функцию XLOOKUP с несколькими критериями?

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

1. Конкатенация значений

1. Создайте таблицу с несколькими столбцами. В примере мы используем значения для поиска в ячейках F2 и G2.

2. Введите в ячейку формулу:

=XLOOKUP(F2&G2, A2:A5&B2:B5, C2:C5)

3. Нажмите Enter.

Пояснение:

• F2&G2 — объединяет искомые значения в одну строку.
• A2:A5&B2:B5 — объединяет параллельные диапазоны для сравнения.
• C2:C5 — диапазон с возвращаемыми значениями.

В примере строки с «Blue» и «Jeans» совпадают на третьей строке, поэтому возвращается 40.

Важно: при использовании конкатенации не забывайте о пробелах и форматах (например, даты и числа). Если один критерий может содержать пустые строки, добавляйте явный разделитель: F2&"|"&G2 и A2:A5&"|"&B2:B5.

2. Булевы выражения (логическое умножение)

1. Берём ту же таблицу.
2. В нужной ячейке вводим формулу:

=XLOOKUP(1, (A2:A5=F2)*(B2:B5=G2), C2:C5)

3. Нажмите Enter.

Пояснение:

• 1 — искомое логическое значение (TRUE представлено как 1).
• (A2:A5=F2)*(B2:B5=G2) — создаёт массив 1/0, где 1 означает совпадение по обоим условиям (логическое «И»).
• C2:C5 — диапазон для возврата.

Булевый способ устойчивее к форматам, так как сравнивает значения по отдельности, а не строкой.

Что делать, если есть несколько совпадений?

XLOOKUP возвращает только первое совпадение в найденном порядке. Если нужно вернуть все совпадения — используйте функцию FILTER (только в Excel с динамическими массивами):

=FILTER(C2:C10, (A2:A10=F2)*(B2:B10=G2))

FILTER вернёт массив значений (несколько строк). Если в результате нет совпадений, можно добавить запасной ответ:

=IFERROR(FILTER(C2:C10, (A2:A10=F2)*(B2:B10=G2)), "Совпадений не найдено")

Можно ли XLOOKUP вернуть несколько значений?

Нет. XLOOKUP возвращает одно значение (либо одну ссылку/ячейку). Для списка значений используйте FILTER или комбинируйте INDEX с SMALL/AGGREGATE в старых версиях Excel.

XLOOKUP или VLOOKUP — что лучше?

XLOOKUP обычно лучше: поиски вправо/влево, точный поиск по умолчанию, удобные аргументы замены при ошибке, совместимость с динамическими массивами. VLOOKUP остаётся полезным в старых шаблонах или если нужна обратная совместимость с очень старым Excel.

Как применять XLOOKUP к нескольким столбцам?

Используйте либо конкатенацию (&), либо логические выражения ((диапазон=критерий)*(...)). Убедитесь, что все диапазоны одинаковой длины. Если диапазоны разной длины, Excel вернёт ошибку #SPILL! или #VALUE!.

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

• FILTER: возвращает все совпадения и подходит для динамических массивов.
• INDEX + MATCH с SUMPRODUCT или с объединёнными критериями: работает в старых версиях Excel.
• Вспомогательный столбец: создайте колонку, где объединяете ключи (=A2&"|"&B2) и используйте обычный XLOOKUP или VLOOKUP по этому столбцу.

Пример INDEX+MATCH для одного совпадения без XLOOKUP (если XLOOKUP недоступен):

=INDEX(C2:C10, MATCH(1, (A2:A10=F2)*(B2:B10=G2), 0))

Этот вариант требует ввода как формулы массива в старых Excel (Ctrl+Shift+Enter) или будет работать автоматически в современных.

Когда такие приёмы не работают (контрпримеры)

• Диапазоны разной длины: A2:A10 и B2:B9 — ошибка.
• Наличие скрытых пробелов и разных форматов (текст против числа) приведёт к отсутствию совпадений.
• XLOOKUP недоступен в Excel 2016 и ниже — используйте INDEX+MATCH или вспомогательные столбцы.
• Конкатенация может создавать коллизии ключей ("AB"&"C" и "A"&"BC") — используйте разделитель.

Быстрая шпаргалка (cheat sheet)

• Поиск по двум полям (XLOOKUP + булевы): =XLOOKUP(1, (A=A_что)*(B=B_что), C)
• Поиск по двум полям (конкатенация): =XLOOKUP(key1&"|"&key2, A&A2&"|"&B2, C)
• Вернуть все совпадения: =FILTER(C:C, (A:A=..)*(B:B=..))
• Старый Excel (INDEX+MATCH): =INDEX(C:C, MATCH(1, (A:A=..)*(B:B=..), 0))
• Защита от ошибок: оберните в IFERROR(..., "Сообщение").

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

Аналитик:

• Убедиться в одинаковых форматах (дата/число/текст).
• Проверить уникальность ключей или необходимость возврата множества значений.
• Добавить IFERROR с понятным сообщением.

Бухгалтер:

• Использовать разделители при конкатенации для надёжности.
• Проверить влияние округлений и форматов.

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

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

Краткая методология внедрения

1. Проверить версию Excel и поддержку XLOOKUP/FILTER.
2. Подготовить и выровнять диапазоны (одинаковая длина, одинаковые форматы).
3. Выбрать подход: конкатенация (простота) или булевы выражения (надежность).
4. Протестировать на образцах с несколькими совпадениями.
5. Добавить обработку ошибок и комментарии для коллег.

Совместимость и советы по миграции

• XLOOKUP и FILTER поддерживаются в Microsoft 365 и более новых релизах Excel. Для пользователей Excel 2016 и ниже предоставьте альтернативы (INDEX+MATCH или вспомогательные столбцы) и пометьте листы как несовместимые.
• При экспорте/импорте в другие инструменты (CSV, BI) заранее создавайте ключевые поля в данных, а не в формулах.

Важно: всегда проверяйте, что столбцы, объединяемые через &, не содержат лишних пробелов. Для чистки используйте TRIM и VALUE при необходимости.

Отличия XLOOKUP и FILTER — когда что выбрать

• Нужен только первый совпадающий результат и обратная совместимость поведения — XLOOKUP.
• Нужен список всех совпадений — FILTER.
• У вас старый Excel — INDEX+MATCH или вспомогательный столбец.

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

XLOOKUP — современная функция поиска в Excel, заменяющая VLOOKUP/HLOOKUP; FILTER — функция для возврата множества строк по критериям; булевы выражения — логические массивы 1/0, используемые для комбинированного поиска.

Резюме

• XLOOKUP можно использовать с несколькими критериями через конкатенацию или булевы выражения.
• Для возврата всех совпадений используйте FILTER.
• Для старых версий Excel используйте INDEX+MATCH или вспомогательный столбец.

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

Источник: практические приёмы работы с формулами Excel и типичные сценарии использования XLOOKUP и FILTER.