Excel - Функції XLOOKUP проти VLOOKUP проти INDEX / MATCH

28 серпня 2022 року Microsoft оголосила про вихід функції XLOOKUP. Функція XLOOKUP призначена для заміни функцій VLOOKUP та HLOOKUP. Він також замінить комбо функції INDEX / MATCH, яке раніше використовувалося для виконання більш потужних VLOOKUP.

Функція XLOOKUP повільно надходить користувачам Office 365 (починаючи з інсайдерів Office 365). Тож ви, можливо, ще не побачите нової функції. Користувачі Office 2022 не отримають XLOOKUP. Тому будьте обережні з використанням функції XLOOKUP - переконайтесь, що ваші кінцеві користувачі мають доступ до нової функції.

Приклад XLOOKUP

Синтаксис XLOOKUP

Синтаксис XLOOKUP:

XLOOKUP (lookup_value, lookup_array, return_array, [match_mode], [search_mode])

Де:

• lookup_value - на що звернути увагу
• lookup_array - Де шукати
• return_array - Що вивести
• [режим_збігу] - (НЕОБОВ’ЯЗКОВО) Укажіть тип відповідності для виконання. За замовчуванням використовується точна відповідність (усі параметри див. У таблиці нижче)
• [режим пошуку] - (НЕОБОВ’ЯЗКОВО) Вкажіть тип та напрямок пошуку. За замовчуванням від першого до останнього (див. Таблицю нижче для всіх опцій)

XLOOKUP Match_Mode

0 - Точне збіг знайде лише точні збіги

1 (-1) - Виконає точну відповідність або знайде наступний найбільший (найменший) предмет.

2 - Зміна символів підстановки дозволяє використовувати? або * символи підстановки для неточних збігів.

XLOOKUP Search_Mode

1 - Шукайте зверху вниз (або зліва направо для горизонтального пошуку)

-1 - Шукайте знизу вгору (або справа наліво для горизонтального пошуку)

2 (-2) - Двійковий пошук за відсортованими даними. Якщо ви не знаєте, що таке двійковий пошук, вам, ймовірно, ніколи не доведеться його виконувати.

Чому XLOOKUP краще, ніж VLOOKUP?

1. Функція VLOOKUP вимагає, щоб стовпець пошуку був самим лівим стовпцем у наборі даних. Ви не можете "шукати вліво". Це була одна з основних переваг використання INDEX / MATCH замість VLOOKUP. Однак XLOOKUP не має цього обмеження
2. За замовчуванням XLOOKUP має точну відповідність. За замовчуванням VLOOKUP має значення "приблизного" збігу, вимагаючи додати аргумент "false" в кінці вашого VLOOKUP для виконання точного збігу. Це стало причиною незліченних помилок електронних таблиць, коли користувачі ненавмисно виконували приблизні збіги.
3. Формули VLOOKUP не можуть обробляти вставки або видалення стовпців. Якщо ви вставили або видалили стовпець, вам потрібно буде змінити номер індексу стовпця у вашому VLOOKUP. Це не проблема з функцією XLOOKUP.
4. XLOOKUP може виконувати горизонтальний або вертикальний пошук. XLOOKUP замінює VLOOKUP і HLOOKUP.
5. XLOOKUP має розумніші приблизні збіги. Дані VLOOKUP потрібно сортувати від найменшого до найбільшого. Однак XLOOKUP може виконувати пошук у будь -якому напрямку.
6. XLOOKUP вимагає посилання на менше клітинок. VLOOKUP вимагав від вас введення всього набору даних, але XLOOKUP вимагає лише посилання на відповідні стовпці або рядки. Посилаючись на меншу кількість осередків, XLOOKUP збільшить швидкість обчислення вашої електронної таблиці і потенційно призведе до меншої кількості помилок кругового посилання.

Приклади XLOOKUP у VBA

XLOOKUP наразі доступний лише для інсайдерської програми Office 365, тому він ще не є загальнодоступним. Незабаром він стане доступним.