Обобщение
За да търсите данни въз основа на множество сложни критерии, можете да използвате функцията XLOOKUP с множество изрази, базирани на логическа логика. В показания пример формулата в G5 е:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
С настройките по подразбиране на XLOOKUP за режим на съвпадение (точно) и режим на търсене (от първо до последно) формулата съответства на първия запис, където:
акаунтът започва с "x" И регионът е "изток", а месецът НЕ е април.
което е четвъртият запис (ред 8) в показания пример.
Обяснение
Обикновено функцията XLOOKUP е конфигурирана да търси стойност в масив за търсене, който съществува на работния лист. Когато обаче критериите, използвани за съвпадение на стойност, станат по-сложни, можете да използвате логическа логика, за да създадете масив за търсене в движение, съставен само от 1s и 0s, след това да потърсите стойността 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)
