Функція XLOOKUP
Скористайтеся функцією XLOOKUP , щоб знайти об'єктів у таблиці або діапазоні за рядком. Наприклад, знайдіть ціну автомобільної частини на номер деталі або знайдіть ім'я працівника на основі його ідентифікатора працівника. Функція XLOOKUP дає змогу шукати пошуковий запит в одному стовпці в одному стовпці та повертати результат із того самого рядка в іншому стовпці, незалежно від того, з якого боку ввімкнуто зворотний стовпець.
Примітка.: Функція XLOOKUP недоступна в Excel 2016 та Excel 2019, однак може виникнути ситуація використання книги в Excel 2016 або Excel 2019 із функцією XLOOKUP, створеною іншим користувачем за допомогою новішої версії Програми Excel.
Синтаксис
Функція XLOOKUP шукає діапазон або масив, а потім повертає елемент, який відповідає першому збігу, який він знаходить. Якщо збігів немає, функція XLOOKUP може повернути найближчий (приблизний) збіг.
=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])
|
Аргумент |
Опис |
|---|---|
|
lookup_value Необхідні* |
Значення для пошуку |
|
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, а потім шукає Qtr1 у верхньому рядку таблиці (діапазон 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 Біллу Джелену за те, що він запропонував цей приклад.
———————————————————————————
Додаткові відомості
Ви завжди можете поставити запитання експерту в спільноті Tech у розділі Excel чи отримати підтримку в спільнотах.
Потрібна додаткова довідка?
Потрібні додаткові параметри?
Ознайомтеся з перевагами передплати, перегляньте навчальні курси, дізнайтесь, як захистити свій пристрій тощо.
Спільноти допомагають ставити запитання й відповідати на них, надавати відгуки та дізнаватися думки висококваліфікованих експертів.
