Как использовать функцию Excel XLOOKUP -

Резюме

Функция Excel XLOOKUP - это современная и гибкая замена старых функций, таких как ВПР, ГПР и ПРОСМОТР. XLOOKUP поддерживает приблизительное и точное соответствие, символы подстановки (*?) Для частичных совпадений и поиск в вертикальных или горизонтальных диапазонах.

Цель

Значения поиска в диапазоне или массиве

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

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

Синтаксис

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

Аргументы

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

Версия

Excel 365

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

XLOOKUP - это современная замена функции VLOOKUP. Это гибкая и универсальная функция, которую можно использовать в самых разных ситуациях.

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

Сообщение не найдено

Когда XLOOKUP не может найти совпадение, он возвращает ошибку # Н / Д, как и другие функции сопоставления в 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 используется для получения данных о продажах на основе точного совпадения в Movie. Формула в 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. Чтобы отобразить настраиваемое сообщение вместо # Н / Д, укажите значение для необязательного аргумента «не найдено», заключенное в двойные кавычки («»). Например, чтобы отобразить сообщение «Не найдено», когда соответствующий фильм не найден, на основании таблицы ниже используйте:

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

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

Пример # 6 - сложные критерии

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

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

Подробности: (1) простой пример, (2) более сложный пример.

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

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

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

Заметки

XLOOKUP может работать как с вертикальными, так и с горизонтальными массивами.
XLOOKUP вернет # Н / Д, если искомое значение не найдено.
У lookup_array должно быть измерение, совместимое с аргументом return_array , иначе XLOOKUP вернет #VALUE!
Если между книгами используется XLOOKUP, обе книги должны быть открыты, иначе XLOOKUP вернет #REF !.
Как и функция ИНДЕКС, XLOOKUP в качестве результата возвращает ссылку.