Функція XLOOKUP (ПРОСМОТРХ) | Google Sheets | Excel

 Функція XLOOKUP чи ПРОСМОТРX – це нова функція пошуку в Google Таблицях, яка є потужнішою та гнучкішою, ніж попередні функції пошуку, такі як VLOOKUP або HLOOKUP.
У MS Excel функція доступна у версії 365.

XLOOKUP шукає відповідність ключу пошуку в діапазоні пошуку та повертає значення з діапазону результатів у тій же позиції.
Для роботи функції потрібно мінімум три аргументи і максимум шість.
=XLOOKUP(
ключ_пошуку;         - Значення, яке потрібно шукати
діапазон_пошуку;     - Це має бути один стовпець або один рядок.
діапазон_результату; - Діапазон, з якого треба отримати результат.
                       Повернене значення береться з позиції відповідного значення в масиві пошуку.
                       Діапазон результатів має бути одного розміру з діапазоном пошуку.
[немає_значення];    - Значення, яке буде замість помилки #N/A , якщо збігів немає. Це необов’язковий аргумент.
[режим_збігу];       - Цей необов’язковий аргумент дозволяє вказати, який режим відповідності використовувати.
                       Якщо не вказано, використовується точний збіг.
[режим_пошуку])      - Цей необов’язковий аргумент, керує режимом пошуку. Якщо не вказувати шукає з початку до кінця.
   
Якщо XLOOKUP не знайде відповідності, ви можете вказати значення за замовчуванням.
Ви можете керувати режимом відповідності, як і іншими функціями пошуку, і навіть керувати режимом пошуку.
Детальніше про це нижче, але спочатку давайте розглянемо простий приклад.

Ось проста формула XLOOKUP,яка шукає ключ- Значення для пошуку в стовпці B і повертає значення зі стовпця D:

=XLOOKUP(F7;B4:B13;D4:D13)  та її порівняння з =VLOOKUP(F4;B4:D13;3;0) 

На аркуші це виглядає так:

1. XLOOKUP - Базовий точний збіг

• Діапазон пошуку може бути лише одним рядком або одним стовпцем. Це не може бути масив із кількома рядками та стовпцями.

Діапазон результатів має бути сумісним із розміром діапазону пошуку. Наприклад, якщо діапазон пошуку – це стовпець даних із 10 рядками та 1 стовпцем, тоді діапазон результатів також має містити 10 рядків (хоча він може мати більше 1 стовпця).

2. Тепер ми можемо вказати значення для помилки #N/A, якщо відповідності не знайдено. Це робиться за допомогою четвертого / (необов’язкового) аргументу, наприклад:  =XLOOKUP(F19;B19:B28;D19:D28;"відсутнє") 

У цьому випадку ключ пошуку "XXX333" не знайдено в масиві пошуку (стовпець B), тому функція XLOOKUP повертає текст замість помилки #N/A, який ми встановили як "відсутнє".

3. В XLOOKUP діапазон пошуку справа, і взагалі де завгодно.    =XLOOKUP(F35;D35:D44;B35:B44)
Великим плюсом функції є те, що їй все одно в якому порядку розташовані діапазони.

Формула не зміниться, але цього разу діапазон результатів розташований ліворуч від нашого діапазону пошуку і це не заважає правильному результату.

4. XLOOKUP приблизний збіг
П'ятий аргумент функції XLOOKUP визначає режим точності пошуку. Якщо його опустити або встановити на 0, виконується точний збіг. Однак є ситуації, коли варіант приблизної відповідності працює дуже добре.

Розглянемо випадок, коли наш пошуковий ключ знаходиться між двома значеннями в діапазоні пошуку. Це не точний збіг, але ми все одно можемо захотіти повернути результат, щоб сказати, що він нижчий за X або вищий за Y.

Наприклад, розглянемо двох варіантів надання знижок по сумі замовлення, у першому варіанті знижка надається на суму від, тобто на замовлення від 1000 діє знижка 1% і так далі.
=XLOOKUP(G51;G54:G57;H54:H57;"-";-1)
Зверніть увагу на -1 як останній аргумент, який вказує функції шукати точну відповідність і, якщо вона її не знаходить, повертати значення, яке знаходиться нижче в масиві.
Тобто 10 800 більше 8 000, але менше 100 000, тому бере відсоток від нижчого елемента, 3% 

Але життя перемінливе і система знижок теж, тепер знижки працюють на суму до, тобто на замовлення до 1000 діє знижка 1% і так далі.
Щоб працювала формула змінюємо останній аргумент на 1 з плюсом, який вказує функції шукати точну відповідність і, якщо вона її не знаходить, повертати значення, яке знаходиться вище в масиві.

5. XLOOKUP пошук з підстановкою символів  - функція підтримує три символи підстановки: *, ? і ~, щоб застосовувати такий пошук 5й елемент змінюємо на 2 - команда шукай по символьно.

Ситуація із зірочкою - відоме тільки прізвище Клієнта, потрібно знайти про нього усю інформацію.
Для цього і є - Зірочка * відповідає нулю або більше символів.
Тому вносимо прізвище так "Косовець*" - все, що після зірочки не важливо.
=XLOOKUP("Косовець*";E66:E75;E66:E75;"-";2) 

Ситуація із знаком питання - номер замовлення затерто, відомо декілька символів, потрібно знайти про нього усю інформацію.
Знак питання ? - відповідає точно одному символу.
Тому вносимо номер так "?GCN???" - всі затерті - невідомі символи замінюємо на "?".
=XLOOKUP("?GCN???";D66:D75;E66:E75;"-";2) 

Ситуація із знаком Тильда - це коли у значенні по якому шукаєш є символи і ?, і *. В цьому випадку використовується "~".
Тильда - це екранований символ, який дає змогу шукати * або ? замість використання їх як символів підстановки.
Давайте подивимося приклад, у якому для пошуку повного імені використовується номер замовлення PX** , програма розуміє його як знади мені все до зірочки, і знаходить неправильне значення, а нам потрібен точний збіг.

Тому вносимо номер так "PX~*~*" - всі зірочки після тильди.
=XLOOKUP("PX~*~*";D66:D75;E66:E75;"-";2) 

6. XLOOKUP виведення масиву даних (стовпчик чи рядок). Функція XLOOKUP може повертати кілька результатів для одного збігу, а не лише один результат, як VLOOKUP.

Результат стовпець: =XLOOKUP(G82;A81:E81;A82:E91;"-";0;-1) 

Результат рядок: =XLOOKUP(A93;A82:A91;B82:E91;"-") 

7. XLOOKUP зміна напрямку пошуку. Останній аргумент дозволяє змінити використовуваний метод пошуку. За замовчуванням пошук здійснюється згори донизу діапазону, але ви можете змінити це на пошук знизу до верху, якщо це має сенс.
Наприклад відсортований діапазон.

Дивимось як вони працюють на YouTube каналі тут, а приклад тут.