Резюме
Для пошуку даних на основі декількох складних критеріїв ви можете використовувати функцію XLOOKUP з кількома виразами на основі логічної логіки. У наведеному прикладі формула в G5 має вигляд:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
З типовими налаштуваннями XLOOKUP для режиму збігу (точного) та режиму пошуку (від першого до останнього) формула відповідає першому запису, де:
рахунок починається з "x" І регіон "схід", а місяць НЕ Квітень.
що є четвертим записом (рядок 8) у наведеному прикладі.
Пояснення
Зазвичай функція XLOOKUP налаштована на пошук значення в масиві підстановки, який існує на аркуші. Однак, коли критерії, що використовуються для зіставлення значення, ускладнюються, ви можете використовувати логічну логіку для створення масиву підказки на льоту, що складається лише з 1 і 0, а потім шукайте значення 1. Це підхід, використаний у цьому прикладі:
=XLOOKUP(1,boolean_array,result_array)
У цьому прикладі необхідними критеріями є:
рахунок починається з "x" І регіон "схід", а місяць НЕ Квітень.
Для кожного з трьох окремих критеріїв, наведених вище, ми використовуємо окремий логічний вираз. Перший вираз використовує функцію LEFT, щоб перевірити, чи починається Рахунок з "x":
LEFT(B5:B16)="x" // account begins with "x"
Оскільки ми перевіряємо дванадцять значень, результатом є масив із дванадцятьма значеннями, як це:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Другий вираз перевіряє, чи є регіон "східним", використовуючи оператор дорівнює (=):
C5:C16="east" // region is east
Як і раніше, ми отримуємо ще один масив із дванадцятьма значеннями TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Третій вираз повинен виключати місяць квітень. Найпростіший спосіб зробити це - провести тест за квітень безпосередньо за допомогою функції МІСЯЦЬ:
MONTH(D5:D16)=4 // month is April
Потім за допомогою функції NOT поверніть результат назад:
NOT(MONTH(D5:D16)=4) // month is not April
який створює масив, що правильно описує "не квітень":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Далі всі три масиви множаться разом, і математична операція примушує значення TRUE і FALSE до 1s і 0s:
(1;0;1;1;1;0;0;0;1;1;0;1)* (0;0;1;1;1;0;1;0;0;1;0;1)* (0;0;0;1;1;1;1;1;1;1;1;1)
У булевій арифметиці множення працює як логічна функція І, тому кінцевим результатом є один масив, такий:
(0;0;0;1;1;0;0;0;0;1;0;1)
Формулу тепер можна переписати так:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
З 1 як значення підстановки та налаштуваннями за замовчуванням для режиму збігу (точного) та режиму пошуку (від першого до останнього), XLOOKUP відповідає першому 1 (четверта позиція) і повертає відповідний рядок у масиві результатів, який є B8: E8.
Останній матч
Встановивши для необов'язкового аргументу режиму пошуку значення -1, ви можете знайти "останню відповідність" з тими ж критеріями, як цей:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
