За допомогою функції XLOOKUP можна знаходити елементи в таблиці або діапазоні за рядком. Наприклад, можна знайти ціну на запчастину до автомобіля за її номером або знайти ім'я працівника за його ідентифікаційним номером. Функція XLOOKUP дає змогу шукати слово в одному стовпці та повернути результат із того самого рядка в іншому стовпці, незалежно від того, з якого боку розташовано стовпець повернення.
Примітка.
Функція XLOOKUP недоступна в Excel 2016 і Excel 2019. Однак може виникнути ситуація використання книги в Excel 2016Excel 2016 або Excel 2019 з функцією XLOOKUP, якщо її створив інший користувач у новішій версії Excel.
Синтаксис
Функція XLOOKUP виконує пошук у діапазоні або масиві та повертає елемент, що відповідає першому знайденому збігу. Якщо збігу немає, функція XLOOKUP може повернути найближчий (приблизний) збіг.
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Аргумент | Опис |
|---|---|
|
lookup_value Обов'язково* |
Значення, яке потрібно шукати *Якщо його не вказано, функція XLOOKUP повертає пусті клітинки, знайдені в lookup_array. |
|
lookup_array Обов’язковий |
Масив або діапазон, які потрібно знайти |
|
return_array Обов’язковий |
Масив або діапазон, які потрібно повернути. |
|
[if_not_found] Необов’язковий |
Якщо дійсний збіг не знайдено, повертається введений текст [if_not_found]. Якщо не знайдено припустимого збігу та не вказано значення [if_not_found], повертається значення #N/A . |
|
[match_mode] Необов’язковий |
Укажіть тип збігу: 0 – точний збіг. Якщо нічого не знайдено, поверніть #N/A. Цей параметр установлено за промовчанням. -1 – точний збіг. Якщо нічого не знайдено, поверніть наступний, менший елемент. 1 – точний збіг. Якщо нічого не знайдено, поверніть наступний, більший елемент. 2 – збіг символу підстановки, де *, ? та ~ мають особливе значення |
|
[search_mode] Необов’язковий |
Укажіть потрібний режим пошуку: 1 – виконати пошук, починаючи з першого елемента. Цей параметр установлено за промовчанням. -1 - Виконати зворотний пошук, починаючи з останнього елемента. 2 – здійснити бінарний пошук, який покладається на те, що lookup_array відсортовано за зростанням . Якщо його невідсортовано, буде повернено недійсні результати. 2 – здійснити бінарний пошук, який покладається на те, що масив lookup_array посортовано за спаданням. Якщо його невідсортовано, буде повернено недійсні результати. |
Приклади
У прикладі 1 функція XLOOKUP використовується, щоб знайти назву країни в діапазоні, а потім повернути відповідний код країни по телефону. Він містить аргументи lookup_value (клітинка F2), lookup_array (діапазон B2:B11) і return_array (діапазон D2:D11). Аргумент match_mode відсутній, тому що функція XLOOKUP за замовчуванням забезпечує точний збіг.
Примітка.
Функції XLOOKUP використовують масив підстановки та масив повернення, тоді як функція VLOOKUP використовує один масив таблиць, за яким указується номер індексу стовпця. Еквівалентна формула VLOOKUP у цьому випадку матиме такий вигляд: =VLOOKUP(F2;B2:D11;3;FALSE)
———————————————————————————
Приклад 2 : пошук відомостей про працівника за його ідентифікаційним номером. На відміну від функції VLOOKUP, функція XLOOKUP може повертати масив із кількома елементами, тому одна формула може повернути ім'я працівника та відділ із клітинок C5:D14.
———————————————————————————
Приклад 3 додає аргумент if_not_found до попереднього прикладу.
———————————————————————————
Приклад 4 виконує пошук у стовпці C даних про доходи фізичних осіб, введені в клітинку E2, і знаходить відповідну податкову ставку в стовпці B. Він задає аргументу if_not_found значення «повернути 0 » (нуль), якщо нічого не знайдено. Аргумент match_mode має значення 1, тобто функція шукатиме точний збіг, і якщо не зможе знайти його, функція поверне наступний більший елемент. Нарешті, аргумент search_mode має значення 1, тобто функція шукатиме від першого до останнього елемента.
Примітка.
Стовпець lookup_array XARRAY знаходиться праворуч від стовпця return_array, тоді як VLOOKUP може дивитися лише зліва направо.
———————————————————————————
У прикладі 5 вкладена функція XLOOKUP використовується для пошуку як вертикальних, так і горизонтальних зіставлень. Спочатку шукається валовий прибуток у стовпці B, потім – значення " Кв. 1 " у верхньому рядку таблиці (діапазон C5:F5) і нарешті повертає значення на перетині цих стовпців. Це схоже на використання функцій INDEX і MATCH разом.
Порада.
Функцію HLOOKUP також можна замінити за допомогою функції XLOOKUP .
Примітка.
У клітинках D3:F3 введено формулу: =XLOOKUP(D2;$B 6:$B 17;XLOOKUP($C 3;$C 5:$G 5;$C 6:$G 17)).
———————————————————————————
У прикладі 6 для підсумовування всіх значень між двома діапазонами використовуються функція SUM і дві вкладені функції XLOOKUP. У цьому випадку ми хочемо підсумувати значення для винограду, бананів і включити груші, які знаходяться між ними.
Клітинка E3 містить формулу: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))
Принцип роботи Функція XLOOKUP повертає діапазон, тому під час обчислення формула матиме такий вигляд: =SUM($E$7:$E$9) Щоб побачити, як це працює, самостійно виберіть клітинку з формулою XLOOKUP, схожою на цю, виберіть "Формули>Аудит формули>"Обчислити формулу, а потім натисніть кнопку "Обчислити", щоб виконати обчислення.
Примітка.
Дякуємо спеціалісту MVP з Microsoft Excel Біллу Джелену (Bill Jelen), що запропонував цей приклад.
———————————————————————————