Podsumowanie
Aby wyszukiwać dane w oparciu o wiele złożonych kryteriów, możesz użyć funkcji XLOOKUP z wieloma wyrażeniami opartymi na logice boolowskiej. W pokazanym przykładzie formuła w G5 to:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Przy domyślnych ustawieniach XLOOKUP dla trybu dopasowania (dokładnego) i trybu wyszukiwania (od pierwszego do ostatniego), formuła pasuje do pierwszego rekordu, gdzie:
konto zaczyna się od „x” ORAZ region to „wschód”, a miesiącem NIE jest kwiecień.
co jest czwartym rekordem (wiersz 8) w pokazanym przykładzie.
Wyjaśnienie
Zwykle funkcja XLOOKUP jest skonfigurowana do wyszukiwania wartości w tablicy wyszukiwania, która istnieje w arkuszu. Jednak gdy kryteria używane do dopasowania wartości stają się bardziej złożone, można użyć logiki boolowskiej, aby utworzyć w locie tablicę wyszukiwania składającą się tylko z jedynek i zer, a następnie poszukać wartości 1. Oto podejście zastosowane w tym przykładzie:
=XLOOKUP(1,boolean_array,result_array)
W tym przykładzie wymagane kryteria to:
konto zaczyna się od „x” ORAZ region to „wschód”, a miesiącem NIE jest kwiecień.
Dla każdego z trzech oddzielnych kryteriów powyżej używamy oddzielnego wyrażenia logicznego. Pierwsze wyrażenie używa funkcji LEFT, aby sprawdzić, czy konto zaczyna się od „x”:
LEFT(B5:B16)="x" // account begins with "x"
Ponieważ sprawdzamy dwanaście wartości, wynikiem jest tablica zawierająca dwanaście wartości, takich jak ta:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Drugie wyrażenie sprawdza, czy Region ma wartość „wschód”, używając operatora równości (=):
C5:C16="east" // region is east
Jak poprzednio, otrzymujemy kolejną tablicę z dwunastoma wartościami TRUE FALSE:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Trzecie wyrażenie musi wykluczać kwiecień. Najłatwiej to zrobić, testując miesiąc kwiecień bezpośrednio za pomocą funkcji MIESIĄC:
MONTH(D5:D16)=4 // month is April
Następnie użyj funkcji NOT, aby odwrócić wynik:
NOT(MONTH(D5:D16)=4) // month is not April
co tworzy tablicę poprawnie opisującą „nie kwiecień”:
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Następnie wszystkie trzy tablice są mnożone razem, a operacja matematyczna przekształca wartości PRAWDA i FAŁSZ na 1 i 0:
(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)
W arytmetyce boolowskiej mnożenie działa jak funkcja logiczna AND, więc ostatecznym wynikiem jest pojedyncza tablica w następujący sposób:
(0;0;0;1;1;0;0;0;0;1;0;1)
Formułę można teraz przepisać w następujący sposób:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Z 1 jako wartością wyszukiwania i domyślnymi ustawieniami trybu dopasowania (dokładnego) i trybu wyszukiwania (od pierwszego do ostatniego), XLOOKUP dopasowuje pierwsze 1 (czwarta pozycja) i zwraca odpowiedni wiersz w tablicy wyników, czyli B8: E8.
Ostatni mecz
Ustawiając opcjonalny argument trybu wyszukiwania na -1, możesz znaleźć „ostatnie dopasowanie” z takimi samymi kryteriami, jak to:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
