XLOOKUP
Функція XLOOKUP шукає діапазон для вказаного значення і повертає значення з того ж рядка в іншому стовпці.
XLOOKUP(значення-пошуку; діапазон-пошуку; діапазон-повернення; якщо-не-знайдено; тип-збігу; тип-пошуку)
значення-пошуку: Шукане значення в аргументі діапазон-пошуку. Значення-пошуку може містити будь-яке значення або рядок REGEX.
діапазон-пошуку: клітинки для пошуку.
діапазон-повернення: клітинки для повернення.
якщо-не-знайдено: необов’язковий аргумент повідомлення, яке відображається, якщо збіг не знайдено.
тип-збігу: необов’язковий аргумент, який вказує, який тип збігу шукати.
точний або наступний найменший (-1): якщо відповідника немає, повертає помилку.
точний відповідник (0 або пропускається): якщо точного відповідника немає, повертає помилку.
точний або наступний найбільший (1): якщо відповідника немає, повертає помилку.
символ узагальнення (2): *, ?, і ~ мають конкретні значення. REGEX можна використовувати у XLOOKUP, лише якщо ви використовуєте символ узагальнення.
тип-пошуку: необов’язковий аргумент, який вказує порядок, в якому шукати в діапазоні.
Бінарний спадний (-2): бінарний пошук, для якого діапазон має бути посортований у спадному порядку, інакше повертається помилка.
Від останнього до першого (-1): пошук в діапазоні від останнього елемента до першого.
Від першого до останнього (1 або пропускається): пошук в діапазоні від першого елемента до останнього.
Бінарний зростаючий (2): бінарний пошук, для якого діапазон має бути посортований у зростаючому порядку, інакше повертається помилка.
Примітки
Якщо або діапазон-пошуку або діапазон-повернення є посиланням з охопленням (наприклад, "B"), колонтитули ігноруються автоматично.
Щоб повернути результати з будь-якого масиву, використовуйте INDEX з XLOOKUP.
Приклад |
|---|
У наведеній нижче таблиці під назвою «Товари» зазначено товари та їх атрибути, як-от розмір і ціна. |
A | B | C | D | E | |
|---|---|---|---|---|---|
1 | Товар | Довжина (см) | Ширина (см) | Вага (кг) | Ціна |
2 | Товар 1 | 16 | 17 | 10 | $82.00 |
3 | Товар 2 | 16 | 20 | 18 | $77.00 |
4 | Товар 3 | 11 | 11 | 15 | $88.00 |
5 | Товар 4 | 15 | 16 | 20 | $63.00 |
Пошук за допомогою XLOOKUP |
|---|
З функцією XLOOKUP ви можете вставляти формулу в електронну таблицю, яка повертатиме будь-яке пов’язане значення, спершу зазначаючи назву товару, а потім — стовпець зі значенням, яке ви хочете повернути. Наприклад, якщо ви хотіли повернути ширину товару 1 у таблиці вище, ви могли скористатися наведеною нижче формулою, яка повертає 17 см.  У цій формулі використовуються такі аргументи:
|
Як налаштувати рядок якщо-не-знайдено |
|---|
Якщо ви хочете пошукати точну довжину товару й повернути його відповідну ширину, а також певний рядок на випадок відсутності збігу, можна скористатися наведеною нижче формулою, яка поверне значення "Немає збігу".  У цій формулі аргумент якщо-не-знайдено використовується для виконання більш конкретного пошуку.
|
Пошук найближчого значення |
|---|
XLOOKUP може також забезпечувати широкий пошук на основі певного значення й наближених до нього значень. Якщо змінити тип-збігу з наведеної вище формули, ви зможете повернути ширину, яка відповідатиме довжині 13 см або наступне менше значення. Наведена нижче формула повертає ширину, що дорівнює 11 см.  У цій формулі аргументи такі ж, як і у прикладі вище, крім значення, що використовується для аргументу тип-збігу, яке змінює спосіб пошуку в таблиці.
|
Як змінити порядок пошуку |
|---|
У деяких випадках може бути корисно змінити порядок пошуку в таблиці за допомогою функції XLOOKUP. Наприклад, у таблиці вище є два товари з довжиною 16 см, тому є два потенційні збіги, якщо шукати значення «16 см» у стовпці «Довжина» за допомогою аргументу значення-пошуку та діапазон-пошуку. Порядок пошуку можна вказати за допомогою наведеної нижче формули, яка повертає значення «20 см».  У формулі нижче аргумент тип-пошуку використовується для вказання порядку, у якому функція XLOOKUP здійснюватиме пошук збігів у таблиці.
|
Використання XLOOKUP з іншими функціями |
|---|
XLOOKUP також можна використовувати з іншими функціями, як-от SUM. Наприклад, ви можете використати формулу, подібну до наведеної нижче, щоб повернути $247 — результат застосування функції SUM до цін на Товар 1, 2 і 3.  У цьому прикладі перша функція XLOOKUP шукає ціну Товару 1, а друга XLOOKUP — ціну Товару 3. Двокрапка (:) між функціями XLOOKUP указує, що SUM має повернути не лише загальну ціну Товару 1 і Товару 3, але й проміжні значення. |
У наведеній нижче формулі XLOOKUP використовується разом із REGEX для повернення Товару 2, першого продукту, ширина якого починається на "2".  У цьому прикладі "символ узагальнення (2)" використовується для типу-збігу для введення символів узагальнення у функцію REGEX. |
Додаткові приклади |
|---|
Задано наведену нижче таблицю: |
A | B | C | |
|---|---|---|---|
1 | Ім’я | Вік | Платня |
2 | Емма | 35 | 71000 |
3 | Юрій | 27 | 81000 |
4 | Зоя | 42 | 86000 |
5 | Софія | 51 | 66000 |
6 | Констянтин | 28 | 52000 |
7 | Том | 49 | 62000 |
8 | Aaron | 63 | 89000 |
9 | Марія | 22 | 34000 |
10 | Еліс | 29 | 52000 |
11 | Борис | 35 | 52500 |
=XLOOKUP(49;B2:B11;C2:C11) повертає "62000", що є платнею першого працівника з віком 49. =XLOOKUP(60000;C2:C11;B2:B11;"Немає") повертає "Немає", оскільки немає працівника з платнею 60000. =XLOOKUP(REGEX("^З.*"); A2:A11; B2:B11; FALSE, 2) повертає “42”, вік «Зої», першого працівника з діапазону, чиє ім’я починається на “З”. =INDEX(XLOOKUP(A2;A2:A11;B2:C11);2) повертає 71000, друге значення в масиві, яке повертає функція XLOOKUP. |