Розширені Застосування XLOOKUP

Використання приблизного співпадіння з XLOOKUP

Точне співпадіння застосовується, коли ідентифікатори повинні збігатися повністю. Однак у деяких бізнес-сценаріях потрібно знаходити значення в межах діапазонів.

Приклад: ставки комісії залежно від порогів продажів.

Sales Table

Якщо продавець має 12,000 продажів, ставка комісії повинна становити 8%. Для цього потрібно використовувати приблизне співпадіння.

Синтаксис із режимом співставлення

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], match_mode)

Опції match_mode:

• 0: точний збіг;
• -1: точний збіг або наступне менше значення;
• 1: точний збіг або наступне більше значення;
• 2: співставлення з використанням шаблонів (wildcard).

Для пошуку за діапазоном використовуйте -1. Це означає, що якщо точного збігу не знайдено, буде повернуто наступне менше значення.

Використання приблизного співставлення

Припустимо, у вас є таблиця оцінювання. Column A містить мінімальний необхідний бал. Column B містить відповідну літерну оцінку. Бали студента вводяться у комірку D2. Мета — повернути правильну літерну оцінку на основі балу. Стовпець мінімальних балів повинен бути відсортований за зростанням.

=XLOOKUP(D2; A3:A8; B3:B8; "Not Found"; -1)

• D2: містить числовий бал студента;
• A3:A8: містить порогові значення мінімальних балів;
• B3:B8: містить літерні оцінки;
• "Not Found": визначає, що відображати, якщо збіг відсутній;
• -1: вказує Excel повернути найближче менше значення, якщо точного збігу не знайдено.

Повернення декількох стовпців

Припустимо, у вас є таблиця співробітників: Employee ID | Salary | Bonus. Необхідно повернути як Salary, так і Bonus за допомогою одного пошуку.

=XLOOKUP(A3;E3:E7;F3:G7)

• A3: містить Employee ID;
• E3:E7: містить Employee ID у таблиці пошуку;
• F3:G7: містить два стовпці Salary та Bonus.

Пошук знизу

Припустимо, у таблиці співробітників з часом з'являються дублікати Employee ID. Найсвіжіший запис завжди додається внизу. За замовчуванням XLOOKUP шукає зверху вниз. Щоб повернути найновіший запис, шукайте знизу вгору.

=XLOOKUP(A2; E3:E7; F3:F7; "Not Found"; 0; -1)

• A2: Employee ID для пошуку;
• E3:E7: стовпець для пошуку;
• F3:F7: стовпець для повернення значення;
• 0: примусово точний збіг;
• -1: пошук знизу вгору.

Сценарій

Робоча книга містить три окремі аркуші.

Ваша мета — застосувати розширений XLOOKUP, щоб отримати конкретні результати.