Функція XLOOKUP в Excel — це потужний інструмент для пошуку та даних у таблиці. Вона замінює старі функції, такі як VLOOKUP, HLOOKUP, пропонуючи більшу гнучкість та простоту використання.

XLOOKUP дозволяє здійснювати пошук об'єктів у таблиці або діапазоні за рядком. Вона шукає в одному стовпці та повертає результат в іншому стовпці незалежно від місцезнаходження шуканого стовпця.

Відмінності XLOOKUP від VLOOKUP

• VLOOKUP шукає у першому стовпчику, тоді як XLOOKUP усе одно, яка структура даних: вона може шукати значення як угору, так і вниз по стовпцю, або вліво та вправо по рядку.

• XLOOKUP не вимагає, щоб стовпець пошуку був першим у таблиці

• XLOOKUP шукає точне співпадіння, а також вміє шукати найближче найбільше число, а VLOOKUP — найближче найменше число

• XLOOKUP може повертати декілька значень, що розташовані в одному або декількох стовпцях або рядках

• На відміну від ВПР, функція ПРОСМОТРX не вимагає сортування даних

• У ПРОСМОТРХ є окремий аргумент для заміни помилок на інше значення у випадках, коли нічого не знайдено

• Функція використовує менше аргументів, що робить її легшою для розуміння та використання

• Втім, VLOOKUP за замовчуванням може працювати із символами підстановки (* та ?), тоді як у XLOOKUP потрібно задавати для цього спеціальний аргумент

• ПРОСМОТРХ не сумісна зі старими версіями Excel

Синтаксис:

=XLOOKUP(lookup_value;lookup_array;return_array;[if_not_found];[match_mode];[search_mode])

lookup_value — що шукаємо

lookup_array — діапазон (масив) пошуку

return_array — діапазон (масив), з якого потрібно повернути значення

[if_not_found] — значення, яке повертається, якщо пошук не дав результату (повертає  #N/A , якщо припустимий збіг не знайдено). Необов’язковий аргумент

[match_mode] — тип збігу (необов’язковий аргумент)

0 - точний збіг,  повертає #N/A, якщо нічого не знайдено

-1 - точний збіг, повертає наступне найменше значення

1 - точний збіг, повертає наступне найбільше значення

2 — частковий збіг з використанням символів підстановки (*, ? або ~)

[search_mode] — режим пошуку (необов’язковий аргумент)

1 — пошук із першого елементу

-1 — зворотній пошук із останнього елементу

2 — сортування за зростанням

-2 — сортування за зменшенням

Приклади використання функції XLOOKUP

1. Точний пошук

У списку з іменами менеджерів нам потрібно знайти кількість проданих товарів менеджером Федоровою Ганною

Формула:

=ПРОСМОТРX(G2;C2:C6;D2:D6)

G2 — шукане значення, комірка з іменем менеджера

C2:C6 — діапазон пошуку (список менеджерів)

D2:D6 — діапазон, з якого потрібно повернути значення (дані про продажі товарів)

Результат: 13

2. Додаємо аргумент if_not_found

Якщо ми помилково введемо ім’я менеджера (замість Федорова Ганна — Федоров Василь), програма поверне введений текст (наприклад, «менеджер не знайдений»)

Результат: «менеджер не знайдений»

3. Приблизний збіг

Необхідно знати відповідну ставку податку для доходу у розмірі 26000 грн

Формула виглядатиме так:

=ПРОСМОТРX(E2;C2:C8;B2:B8;0;1;1)

Е2 — що шукаємо (26000 — розмір доходу)

C2:C8 — діапазон пошуку (стовпчик із сумами максимального граничного доходу)

B2:B8 — діапазон, з якого повертаємо значення (ставка податку)

0 (if_not_found) — повертає 0, якщо нічого не знайдено

1 (match_mode) — функція шукає точний збіг і повертає наступний більший елемент, якщо не знаходить збіг

1 (search_mode) — пошук від першого до останнього елементу

Результат: 22% — найближчий більший збіг

4. Повернення кількох значень

Дуже потужна перевага XLOOKUP перед ВПР: ця функція дозволяє повертати одночасно кілька значень без необхідності створення окремих формул для кожного значення

Наприклад, ми хочемо отримати всю інформацію про менеджера Федорову Ганну (регіон, кількість проданих товарів, прибуток)

Формула:

=ПРОСМОТРX(G2;B2:B6;C2:E6)

G2 — комірка з іменем менеджера

B2:B6 — діапазон пошуку (стовпчик із іменами менеджерів)

C2:E6 — діапазон повернення масиву, що включає три стовпці: регіон, кількість товарів та прибуток

5.Пошук за декількома критеріями

Багатокритеріальний пошук — ще одна суперзручна можливість XLOOKUP

Наприклад, у списку товарів із різними характеристиками (розмір, колір, ціна) нам необхідно вивести значення ціни на білу середню вазу

Щоб з’єднати пошукові значення, скористаємося оператором &

Формула:

=ПРОСМОТРX(G2&H2&I2;B2:B10&C2:C10&D2:D10;E2:E10)

G2&H2&I2 — пошукові значення (ваза + середня + біла)

B2:B10&C2:C10&D2:D10 — діапазон пошуку (стовпчики зі значеннями: назва товару +розмір + колір)

E2:E10 — діапазон повернення (ціна)

Результат: 43

Ми розглянули частину можливостей XLOOKUP. Детально розглядаємо різні кейси використання цієї надпотужної функції на нашому курсі Excel: бізнес-аналіз та прогнозування

Дізнайтеся більше про повну програму "EXCEL: бізнес-аналіз і прогнозування"