Samenvatting
Om gegevens op te zoeken op basis van meerdere complexe criteria, kunt u de functie XLOOKUP gebruiken met meerdere expressies op basis van booleaanse logica. In het getoonde voorbeeld is de formule in G5:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16)
Met de standaardinstellingen van XLOOKUP voor overeenkomstmodus (exact) en zoekmodus (eerste tot laatste) komt de formule overeen met het eerste record waarbij:
account begint met "x" EN regio is "oost", en de maand is NIET april.
dat is het vierde record (rij 8) in het getoonde voorbeeld.
Uitleg
Normaal gesproken is de functie XLOOKUP geconfigureerd om te zoeken naar een waarde in een opzoekmatrix die op het werkblad voorkomt. Wanneer de criteria die worden gebruikt om aan een waarde te voldoen echter complexer worden, kunt u booleaanse logica gebruiken om direct een opzoekmatrix te maken die alleen uit 1s en 0s bestaat en vervolgens naar de waarde 1 zoeken. Dit is de benadering die in dit voorbeeld wordt gebruikt:
=XLOOKUP(1,boolean_array,result_array)
In dit voorbeeld zijn de vereiste criteria:
account begint met "x" EN regio is "oost", en de maand is NIET april.
Voor elk van de drie afzonderlijke criteria hierboven gebruiken we een aparte logische uitdrukking. De eerste uitdrukking gebruikt de LEFT-functie om te testen of Account begint met "x":
LEFT(B5:B16)="x" // account begins with "x"
Omdat we twaalf waarden controleren, is het resultaat een array met twaalf waarden als volgt:
(TRUE;FALSE;TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE;TRUE;FALSE;TRUE)
De tweede uitdrukking test of Regio "oost" is met de operator gelijk aan (=):
C5:C16="east" // region is east
Net als eerder krijgen we nog een array met twaalf TRUE FALSE-waarden:
(FALSE;FALSE;TRUE;TRUE;TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE)
De derde uitdrukking moet de maand april uitsluiten. De eenvoudigste manier om dit te doen, is door rechtstreeks voor de maand april te testen met de functie MAAND:
MONTH(D5:D16)=4 // month is April
Gebruik vervolgens de NOT-functie om het resultaat om te keren:
NOT(MONTH(D5:D16)=4) // month is not April
die een array maakt met een correcte beschrijving van "niet april":
(FALSE;FALSE;FALSE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE;TRUE)
Vervolgens worden alle drie arrays met elkaar vermenigvuldigd, en de wiskundige bewerking dwingt de waarden WAAR en ONWAAR naar 1s en 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)
In booleaanse rekenkunde werkt vermenigvuldiging als de logische functie AND, dus het eindresultaat is een enkele array zoals deze:
(0;0;0;1;1;0;0;0;0;1;0;1)
De formule kan nu als volgt worden herschreven:
=XLOOKUP(1,(0;0;0;1;1;0;0;0;0;1;0;1),B5:E16)
Met 1 als opzoekwaarde en standaardinstellingen voor overeenkomstmodus (exact) en zoekmodus (eerste tot laatste), komt XLOOKUP overeen met de eerste 1 (vierde positie) en retourneert de corresponderende rij in de resultaatmatrix, die B8: E8 is.
Laatste wedstrijd
Door het optionele argument voor de zoekmodus in te stellen op -1, kunt u de "laatste overeenkomst" met dezelfde criteria als volgt lokaliseren:
=XLOOKUP(1,(LEFT(B5:B16)="x")*(C5:C16="east")*NOT(MONTH(D5:D16)=4),B5:E16,,,-1)
