Вся мета XLOOKUP - знайти один результат, швидко знайти його та повернути відповідь у таблицю.
Джо Макдейд, менеджер проекту Excel
Сьогодні опівдні Microsoft почала повільно випускати функцію XLOOKUP для деяких інсайдерів Office 365. Основні переваги XLOOKUP:
- Можна знайти останній збіг!
- Можна дивитись ліворуч!
- За замовчуванням точно відповідає (на відміну від VLOOKUP, який за замовчуванням має значення 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 Підстановочний знак
Варіантом пошуку_режиму є
- 1 від першого до останнього (за замовчуванням)
- -1 від останнього до першого
- 2 двійковий пошук, від першого до останнього (для сортування потрібно масив пошуку)
- -2 двійковий пошук, від останнього до першого (потрібно сортувати масив пошуку)
Заміна простого VLOOKUP
У вас є таблиця пошуку в F3: H30. Таблиця пошуку не сортується.
Ви хочете знайти опис з таблиці.
З VLOOKUP ви б це зробили =VLOOKUP(A2,$F$3:$H$30,3,False). Еквівалент XLOOKUP буде: =XLOOKUP(A2,$F$3:$F$30,$H$3:$H$30).
У XLOOKUP А2 такий самий, як у VLOOKUP.
F3: F30 - це масив пошуку.
H3: H30 - це масив результатів.
В кінці немає потреби в False, оскільки XLOOKUP за замовчуванням точно відповідає!
Одна перевага: якщо хтось вставить новий стовпець у таблицю пошуку, ваш старий VLOOKUP поверне ціну замість опису. XLOOKUP регулюватиме і тримати вказуючи на опис: =XLOOKUP(A2,$F$3:$F$30,$I$3:$I$30).
Знайдіть останній збіг
XLOOKUP дозволяє розпочати пошук з нижньої частини набору даних. Це чудово для пошуку останнього збігу в наборі даних.
Подивіться наліво
Як і LOOKUP та INDEX / MATCH, при XLOOKUP немає клопоту, що дивиться ліворуч від клавіші.
Там, де ви використовували =INDEX($E$3:$E$30,MATCH(A2,$F$3:$F$30,0))раніше, тепер можете використовувати=XLOOKUP(A2,$F$3:$F$30,$E$3:$E$30)
Покращення швидкості XLOOKUP
У наведеному вище прикладі VLOOKUP повинен перерахувати, якщо щось у таблиці підстановки змінюється. Уявіть, якби ваша таблиця включала 12 стовпців. З XLOOKUP формула буде повторювати лише якщо щось у масиві підстановки або масиві результатів зміниться.
Наприкінці 2018 року алгоритм VLOOKUP змінився для швидшого лінійного пошуку. XLOOKUP підтримує ті ж покращення швидкості. Це робить параметри лінійного та двійкового пошуку майже однаковими. Джо Макдейд каже, що використання двоякових параметрів пошуку в Search_Mode не дає значної вигоди.
Підтримка підстановочних знаків, але лише тоді, коли ви цього вимагаєте
Кожен VLOOKUP підтримував символи підстановки, що ускладнює пошук Wal * Mart. За замовчуванням XLOOKUP не використовуватиме символи підстановки. Якщо вам потрібна підтримка узагальнюючих знаків, ви можете вказати 2 як Match_Mode.
Кілька стовпців XLOOKUP
Потрібно зробити 12 стовпців XLOOKUP? Ви можете робити це по одній колонці за раз …
Або, завдяки Dynamic Arrays, поверніть усі 12 стовпців одночасно …
Орієнтовні пошуки більше не потрібно сортувати
Якщо вам потрібно знайти значення трохи менше або просто більше, ніж значення пошуку, таблиці більше не потрібно сортувати.
Або знайти наступне велике значення:
Єдиний недолік: у ваших колег цього не буде (поки)
Завдяки новій політиці польоту, лише деякий невеликий відсоток інсайдерів Office сьогодні має функцію XLOOKUP. Може пройти деякий час, поки функція стане широко доступною, і навіть тоді для неї знадобиться передплата на Office 365. (Динамічні масиви випускаються з вересня 2018 року і досі не розгорнуті до загальної доступності.)
