
Резюме
Функция 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 в качестве результата возвращает ссылку.
Похожие видео



