Sammendrag
For å slå opp data basert på flere komplekse kriterier, kan du bruke XLOOKUP-funksjonen med flere uttrykk basert på boolsk logikk. I eksemplet vist er formelen i G5:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Med XLOOKUPs standardinnstillinger for kampmodus (eksakt) og søkemodus (først til siste) samsvarer formelen med den første posten der:
kontoen begynner med "x" OG regionen er "øst", og måneden er IKKE april.
som er den fjerde posten (rad 8) i eksemplet som vises.
Forklaring
Normalt er XLOOKUP-funksjonen konfigurert til å se etter en verdi i en oppslagsmatrise som finnes på regnearket. Men når kriteriene som brukes for å matche en verdi blir mer komplekse, kan du bruke boolsk logikk til å opprette et oppslagsmatrise i det fulle som bare består av 1s og 0s, og deretter se etter verdien 1. Dette er tilnærmingen som brukes i dette eksemplet:
=XLOOKUP(1,boolean_array,result_array)
I dette eksemplet er de nødvendige kriteriene:
kontoen begynner med "x" OG regionen er "øst", og måneden er IKKE april.
For hvert av de tre separate kriteriene ovenfor bruker vi et eget logisk uttrykk. Det første uttrykket bruker VENSTRE-funksjonen for å teste om Konto begynner med "x":
LEFT(B5:B16)="x" // account begins with "x"
Fordi vi sjekker tolv verdier, er resultatet en matrise med tolv verdier som dette:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
Det andre uttrykket tester om Region er "øst" ved å bruke operatoren lik (=):
C5:C16="east" // region is east
Som før får vi en annen matrise med tolv SANNE FALSE verdier:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
Det tredje uttrykket må ekskludere april måned. Den enkleste måten å gjøre dette på er å teste for april måned direkte med MONTH-funksjonen:
MONTH(D5:D16)=4 // month is April
Bruk deretter IKKE-funksjonen til å reversere resultatet:
NOT(MONTH(D5:D16)=4) // month is not April
som oppretter en matrise som korrekt beskriver "ikke april":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Deretter multipliseres alle tre matriser sammen, og matteoperasjonen tvinger de SANNE og FALSE verdiene til 1s og 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)
I boolsk aritmetikk fungerer multiplikasjon som den logiske funksjonen AND, så det endelige resultatet er en enkelt matrise som dette:
(0;0;0;1;1;0;0;0;0;1;0;1)
Formelen kan nå skrives om slik:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Med 1 som oppslagsverdi, og standardinnstillinger for samsvarsmodus (eksakt) og søkemodus (først til siste), samsvarer XLOOKUP med første 1 (fjerde posisjon) og returnerer tilsvarende rad i resultatmatrisen, som er B8: E8.
Siste kamp
Ved å sette valgfritt argument for søkemodus til -1, kan du finne "siste treff" med samme kriterier som dette:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
