Функция XLOOKUP в Excel

Функция XLOOKUP в Excel — это современная и гибкая замена старых функций, таких как VLOOKUP, HLOOKUP и LOOKUP. XLOOKUP поддерживает приблизительное и точное совпадение, подстановочные знаки (* ?) для частичных совпадений и поиск в вертикальных или горизонтальных диапазонах.

Что делает функция XLOOKUP в Excel?

Функция XLOOKUP в Excel ищет значения в диапазоне или массиве.

Возвращаемое значение

Соответствующие значения из возвращаемого массива

Аргументы

• lookup — искомое значение.
• search_array — Массив или диапазон для поиска.
• return_array — возвращаемый массив или диапазон.
• not_found — [необязательно] Значение, возвращаемое, если совпадение не найдено.
• match_mode — [необязательный] 0 = точное совпадение (по умолчанию), -1 = точное совпадение или следующее наименьшее, 1 = точное совпадение или следующее большее, 2 = совпадение с подстановочными знаками.
• search_mode — [необязательный] 1 = поиск с первого (по умолчанию), -1 = поиск с последнего, 2 = двоичный поиск по возрастанию, -2 = двоичный поиск по убыванию.

Синтаксис

=XLOOKUP(lookup, search_array, return_array, [not_found], [match_mode], [search_mode])

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

XLOOKUP — это современная замена функции VLOOKUP. Это гибкая и универсальная функция, которую можно использовать в самых разных ситуациях. XLOOKUP может находить значения в вертикальных или горизонтальных диапазонах, выполнять приблизительные и точные совпадения, а также поддерживает подстановочные знаки (* ?) для частичных совпадений.

Кроме того, XLOOKUP может искать данные, начиная с первого или последнего значения (см. подробности о типе соответствия и режиме поиска ниже). По сравнению со старыми функциями, такими как VLOOKUP , HLOOKUP и LOOKUP , XLOOKUP предлагает  несколько ключевых преимуществ.

Не найдено сообщение

Если XLOOKUP не может найти совпадение, он возвращает ошибку #N/A, как и другие функции сопоставления в Excel. В отличие от других функций сопоставления, XLOOKUP поддерживает необязательный аргумент not_found, который можно использовать для переопределения ошибки #N/A, если бы она в противном случае появилась. Типичными значениями not_found могут быть «Не найдено», «Нет совпадений», «Нет результата» и т. д. При указании значения not_found заключите текст в двойные кавычки («»).

Примечание. Будьте осторожны, если вы указываете пустую строку («») для not_found. Если совпадение не найдено, XLOOKUP вместо #N/A ничего не отобразит. Если вы хотите видеть ошибку #N/A, когда совпадение не найдено, полностью опустите аргумент.

Тип соответствия

По умолчанию XLOOKUP выполнит точное совпадение. Поведение при совпадении контролируется необязательным аргументом match_type, который имеет следующие параметры:

Тип соответствия	Поведение
0 (по умолчанию)	Полное совпадение. Вернет #N/A, если совпадений нет.
-1	Точное совпадение или следующий меньший элемент.
1	Точное совпадение или следующий больший предмет.
2	Подстановочный знак (*, ?, ~)

Режим поиска

По умолчанию XLOOKUP начнет сопоставление с первого значения данных. Поведение поиска контролируется необязательным аргументом  search_mode, который предоставляет следующие параметры:

Режим поиска	Поведение
1 (по умолчанию)	Поиск по первому значению
-1	Поиск от последнего значения (обратно)
2	Значения двоичного поиска отсортированы по возрастанию.
-2	Значения двоичного поиска отсортированы по убыванию.

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

Пример №1 – базовое точное совпадение

По умолчанию XLOOKUP выполнит точное совпадение. В приведенном ниже примере XLOOKUP используется для получения данных о продажах на основе точного совпадения с фильмом. Формула в H5:

=XLOOKUP(H4,B5:B9,E5:E9)

Пример №2 — базовое приблизительное совпадение

Чтобы включить приблизительное совпадение, укажите значение аргумента match_mode. В приведенном ниже примере XLOOKUP используется для расчета скидки на основе количества, которое требует приблизительного соответствия. Формула в F5 предоставляет -1 для match_mode, чтобы включить приблизительное совпадение с поведением «точное совпадение или следующее наименьшее»:

=XLOOKUP(E5,B5:B9,C5:C9,,-1)

Пример №3 – несколько значений

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

=XLOOKUP(B5,B8:B15,C8:E15)

Обратите внимание, что возвращаемый массив (C8:E15) включает в себя 3 столбца: «Первый», «Последний» и «Отдел». Все три значения возвращаются и попадают в диапазон C5:E5.

Пример №4 — двусторонний поиск

XLOOKUP можно использовать для выполнения двустороннего поиска путем вложения одного XLOOKUP в другой. В приведенном ниже примере «внутренний» XLOOKUP извлекает всю строку (все значения для Glass), которая передается «внешнему» XLOOKUP в качестве возвращаемого массива. Внешний XLOOKUP находит подходящую группу (B) и возвращает соответствующее значение (17,25) в качестве окончательного результата.

=XLOOKUP(I6,C4:F4,XLOOKUP(I5,B5:B9,C5:F9))

Пример №5 – сообщение не найдено

Как и другие функции поиска, если XLOOKUP не находит значение, она возвращает ошибку #N/A. Чтобы отобразить собственное сообщение вместо #N/A, укажите значение необязательного аргумента not_found, заключенное в двойные кавычки («»). Например, чтобы отобразить «Не найдено», когда соответствующий фильм не найден, на основе таблицы ниже, используйте:

=XLOOKUP(H4,B5:B9,E5:E9,"Not found")

Вы можете настроить это сообщение по своему усмотрению: «Нет совпадений», «Фильм не найден» и т. д.

Пример №6 – совпадение с подстановочными знаками

XLOOKUP поддерживает подстановочные знаки, позволяющие осуществлять поиск частичного совпадения. Установите для аргумента match_mode значение 2, чтобы включить подстановочные знаки в XLOOKUP. В приведенном ниже примере XLOOKUP настроен на выполнение сопоставления «содержит подстроку» по названию книг, перечисленных в столбце B. Строка поиска вводится в ячейку G4, а формула в ячейку G6 имеет следующий вид:

=TRANSPOSE(XLOOKUP("*"&G4&"*",data[Title],data,,2))

Пример №7 – сложные критерии

Благодаря возможности естественной обработки массивов, XLOOKUP можно использовать со сложными критериями. В приведенном ниже примере XLOOKUP сопоставляет первую запись, где: учетная запись начинается с «x»,  регион — «восток», а  месяц — не апрель:

=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)

Пример №8 — Бинарный поиск

XLOOKUP имеет опцию режима двоичного поиска, которая выполняет поиск очень быстро. Чтобы включить режим двоичного поиска, данные необходимо отсортировать по возрастанию или убыванию. Если значения отсортированы по возрастанию, используйте значение 2 для search_mode. Если значения отсортированы в порядке убывания, используйте значение -2. Ниже приведен общий синтаксис для включения режима двоичного поиска для поиска точного соответствия:

=XLOOKUP(A1,lookup_array,return_array,,0,2) // бинарный поиск A-Z
=XLOOKUP(A1,lookup_array,return_array,,0,-2) // бинарный поиск Z-A

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

XLOOKUP предлагает несколько важных преимуществ по сравнению с VLOOKUP:

• Может искать данные справа или слева от искомых значений.
• По умолчанию обеспечивает точное совпадение.
• Может работать с вертикальными и горизонтальными данными.
• Может выполнять обратный поиск (от последнего к первому).
• Может возвращать целые строки или столбцы, а не только одно значение.

Примечания

1. XLOOKUP в Excel может работать как с вертикальными, так и с горизонтальными массивами.
2. Функциявернет #N/A, если искомое значение не найдено.
3. Как и функция ИНДЕКС, XLOOKUP возвращает в результате ссылку.
4. Размер lookup_array должен  быть совместим с размером return_array, иначе XLOOKUP вернет #VALUE!
5. Если XLOOKUP указывает на таблицу Excel во внешней книге, другая книга должна быть открыта, иначе XLOOKUP вернет #REF! ошибка.