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

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

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

Таблиця продажів

Якщо продавець має 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. Мета — повернути правильну літерну оцінку на основі балу. Стовпець Minimum Score має бути відсортований за зростанням.

=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 для отримання конкретних результатів.