Вся цель XLOOKUP - найти один результат, быстро найти его и вернуть ответ в электронную таблицу.
Джо МакДэйд, менеджер проектов Excel
Сегодня в полдень Microsoft начала медленно выпускать функцию XLOOKUP для некоторых участников программы предварительной оценки Office 365. Основные преимущества XLOOKUP:
- Могу найти последний матч!
- Можете смотреть налево!
- По умолчанию используется точное совпадение (в отличие от функции ВПР, которая по умолчанию имеет значение True для 4-го аргумента)
- По умолчанию подстановочные знаки не поддерживаются, но вы можете явно разрешить подстановочные знаки, если хотите.
- Все улучшения скорости, выпущенные для VLOOKUP в 2018 году
- Больше не зависит от номера столбца, поэтому он не сломается, если кто-то вставит столбец в середину таблицы поиска.
- Повышение производительности, поскольку вы указываете только два столбца вместо всей таблицы поиска
- XLOOKUP возвращает диапазон вместо VLOOKUP, возвращающего значение
Представляем XLOOKUP
Синтаксис XLOOKUP:
XLOOKUP(Lookup_Value, Lookup_Array, Results_Array, (Match_Mode), (Search_Mode))
Возможны следующие варианты Match_Mode:
- 0 точное совпадение (по умолчанию)
- -1 точное совпадение или следующее меньшее
- 1 точное совпадение или следующее больше
- 2 Подстановочные знаки
Варианты для Search_Mode:
- 1 от первого до последнего (по умолчанию)
- -1 от последней к первой
- 2 бинарный поиск, от первого до последнего (требуется сортировка lookup_array)
- -2 бинарный поиск, от последнего к первому (требуется сортировка lookup_array)
Замена простой ВПР
У вас есть таблица поиска в F3: H30. Таблица поиска не отсортирована.
Вы хотите найти описание в таблице.
С ВПР вам подойдет =VLOOKUP(A2,$F$3:$H$30,3,False). Эквивалент XLOOKUP будет: =XLOOKUP(A2,$F$3:$F$30,$H$3:$H$30).
В XLOOKUP A2 такой же, как в VLOOKUP.
F3: F30 - это поисковый массив.
H3: H30 - это массив результатов.
В конце нет необходимости в False, потому что по умолчанию XLOOKUP имеет точное совпадение!
Одно преимущество: если кто-то вставит новый столбец в таблицу поиска, ваша старая функция VLOOKUP будет возвращать цену вместо описания. XLOOKUP будет регулировать и держать указывая на описание: =XLOOKUP(A2,$F$3:$F$30,$I$3:$I$30).
Найти последний матч
XLOOKUP позволяет начать поиск с нижней части набора данных. Это отлично подходит для поиска последнего совпадения в наборе данных.
Посмотрите налево
Как и ПРОСМОТР и ИНДЕКС / ПОИСКПОЗ, в XLOOKUP нет проблем с просмотром слева от клавиши.
Там, где вы раньше использовали =INDEX($E$3:$E$30,MATCH(A2,$F$3:$F$30,0)), теперь вы можете использовать=XLOOKUP(A2,$F$3:$F$30,$E$3:$E$30)
Улучшение скорости XLOOKUP
В приведенном выше примере ВПР необходимо пересчитать, если что-либо в таблице поиска изменится. Представьте, что в вашей таблице 12 столбцов. С XLOOKUP формула будет пересчитана только в том случае, если что-то в массиве поиска или в массиве результатов изменится.
В конце 2018 года алгоритм ВПР был изменен на более быстрый линейный поиск. XLOOKUP поддерживает те же улучшения скорости. Это делает параметры линейного и двоичного поиска практически идентичными. Джо МакДэйд говорит, что использование параметров двоичного поиска в Search_Mode не дает существенных преимуществ.
Поддержка подстановочных знаков, но только по запросу
Каждая функция VLOOKUP поддерживает подстановочные знаки, что затрудняет поиск Wal * Mart. По умолчанию XLOOKUP не использует подстановочные знаки. Если вам нужна поддержка подстановочных знаков, вы можете указать 2 в качестве Match_Mode.
Несколько столбцов XLOOKUP
Нужно сделать 12 столбцов XLOOKUP? Вы можете делать это по одной колонке за раз…
Или, благодаря динамическим массивам, вернуть все 12 столбцов сразу…
Приблизительные поисковые запросы больше не нужно сортировать
Если вам нужно найти значение чуть меньше или чуть больше искомого значения, таблицы больше не нужно сортировать.
Или найти следующее большее значение:
Единственный недостаток: у ваших коллег этого не будет (пока)
Из-за новой политики Flighting только небольшой процент участников программы предварительной оценки Office сегодня имеет функцию XLOOKUP. Может пройти некоторое время, пока функция не станет широко доступной, и даже тогда для нее потребуется подписка на Office 365. (Динамические массивы были выпущены с сентября 2018 года и до сих пор не стали общедоступными.)
