XLOOKUP() gebruiken met meerdere criteria in Excel

XLOOKUP() in Excel is momenteel de nieuwste functie binnen de groep opzoekfuncties (LOOKUP(), VLOOKUP(), HLOOKUP() en XLOOKUP()) en biedt veel voordelen, extra functionaliteit en meer flexibiliteit.

In deze tutorial bespreken we eerst waar de Excel-functie XLOOKUP() voor dient en waarom die beter is dan de oudere opzoekfuncties; daarna bekijken we de basissyntaxis en vervolgens gaan we naar de kern — het gebruik van de functie XLOOKUP() met meerdere criteria.

Als je de basis van Excel wilt leren, is de beginnersvriendelijke cursus Introduction to Excel de juiste plek om te beginnen.

TL;DR

• XLOOKUP() is de nieuwste opzoekfunctie van Excel. Hij is flexibeler en sneller dan VLOOKUP(), HLOOKUP() en LOOKUP(), met ondersteuning voor multidirectioneel zoeken en gedeeltelijke overeenkomsten.

• Voor zoeken met meerdere criteria heb je twee hoofdbenaderingen: concatenatie (opzoekwaarden samenvoegen met &, bijv. =XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)) of booleaanse expressies (voorwaarden vermenigvuldigen, bijv. =XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)).

• Concatenatie is eenvoudiger en leesbaarder, maar booleaanse expressies geven je meer flexibiliteit.

• Optionele parameters breiden de functie verder uit: [if_not_found] retourneert aangepaste tekst wanneer er niets overeenkomt, [match_mode] maakt benaderende of jokerteken-matches mogelijk, en [search_mode] laat je de zoekrichting omkeren.

Waarom XLOOKUP() gebruiken in Excel

De functie XLOOKUP() doorzoekt een bereik of array met gegevens en retourneert het item dat hoort bij de eerste overeenkomst. Als er geen exacte overeenkomst wordt gevonden, kan XLOOKUP() een benaderende overeenkomst teruggeven, als je een specifiek matchtype opgeeft. In veel opzichten overtreft de functie XLOOKUP() haar voorgangers in Excel (VLOOKUP(), HLOOKUP() en LOOKUP()).

In het bijzonder kun je ermee:

• zowel horizontaal als verticaal en in elke richting zoeken
• meerdere zoekcriteria gebruiken
• een benaderende overeenkomst krijgen, terwijl standaard exact wordt gematcht
• een gedeeltelijke overeenkomst vinden
• meerdere kolommen en rijen retourneren
• aangepaste tekst retourneren wanneer geen overeenkomst wordt gevonden.

Bovendien presteert de functie XLOOKUP() sneller dan de oudere opzoekfuncties in Excel, wat uitmaakt wanneer we binnen een grote hoeveelheid data zoeken.

XLOOKUP() gebruiken met één voorwaarde

Laten we kort de basissyntaxis van de functie XLOOKUP() doornemen:

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

waarbij de vereiste parameters zijn:

• lookup_value—de waarde waarnaar wordt gezocht

• lookup_array—de array of het bereik waarin wordt gezocht

• return_array—de array of het bereik waarvan wordt geretourneerd

en de optionele parameters zijn:

• [if_not_found]—de opgegeven tekst om te retourneren wanneer geen overeenkomst wordt gevonden

• [match_mode]—het matchtype (exact of benaderend) en wat te retourneren als geen overeenkomst is gevonden.

• [search_mode]—de te gebruiken zoekmodus (vooruit of achteruit zoeken, binaire zoekopdracht op de opzoekarray die oplopend of aflopend is gesorteerd).

Voorbeelden van het gebruik van de optionele parameters zie je later in deze tutorial.

Wil je andere handige functies en formules in Excel leren of opfrissen, bekijk dan gerust de tutorial The 15 Basic Excel Formulas Everyone Needs to Know en de Excel Formulas Cheat Sheet.

XLOOKUP() gebruiken met meerdere criteria

Zoals we uit de basissyntaxis zien, is de Excel-functie XLOOKUP() standaard ontworpen om met één opzoekwaarde van een opgegeven variabele te werken.

We kunnen deze functie echter aanpassen om tegelijk op meerdere variables te gebruiken, waarbij voor elke variabele naar een andere opzoekwaarde wordt gezocht. Met andere woorden: we kunnen onze zoekopdracht met XLOOKUP() uitvoeren door meerdere criteria toe te passen. Daarvoor zijn er twee hoofdbenaderingen: concatenatie en booleaanse expressies, en we bespreken ze allebei zo meteen.

Voordat we de technische details induiken, kijken we naar een Excel-tabel waarmee we gaan werken. Deze tabel geeft informatie over 10 katten, inclusief hun namen, kleuren en leeftijden:

In onze experimenten gebruiken we XLOOKUP() om één van de drie kenmerken van een kat te vinden met behulp van de andere twee.

Het is prima dat onze tabel heel simpel is en we in elk geval ook zonder functie kunnen vinden wat we zoeken. Het gaat er hier om de kernprincipes te begrijpen van het gebruik van XLOOKUP() met meerdere criteria. Als je die principes kent, kun je ze makkelijk uitbreiden naar complexere praktijksituaties, bijvoorbeeld wanneer we meer dan twee zoekcriteria hebben of aan specifieke eisen moeten voldoen.

XLOOKUP() met meerdere criteria via concatenatie

De concatenatiebenadering is vrij eenvoudig: we moeten de bijbehorende opzoekwaarden en -arrays met elkaar samenvoegen. Laten we kijken hoe dat werkt.

Stel dat we de leeftijd willen vinden van een wit-met-grijze kat die Nala heet. Voor het gemak hebben we deze twee criteria toegevoegd op hetzelfde blad waar onze tabel staat:

In dit geval is de formule voor XLOOKUP() als volgt:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11)

Die retourneert 2,5, de leeftijd van de eerste wit-met-grijze Nala (mijn kat 😺). Onthoud dat XLOOKUP() het item retourneert dat bij de eerste overeenkomst hoort.

Om te begrijpen waar de eerste twee componenten van de bovenstaande formule voor staan — die met een ampersand — kunnen we ze elk in een aparte Excel-cel uitvoeren:

=B2:B11&C2:C11

Het resultaat is NalaWhite en Grey.

=B2:B11&C2:C11

Het resultaat is:

Ja, zo simpel is het: we hebben simpelweg de bijbehorende opzoekwaarden en opzoekarrays met elkaar samengevoegd, volgens de basis-syntaxis van XLOOKUP() die we eerder bespraken.

XLOOKUP() met meerdere criteria via booleaanse expressies

Laten we nu dezelfde taak uitvoeren — de leeftijd vinden van een wit-met-grijze kat die Nala heet — met de tweede benadering: booleaanse expressies. In dit geval is de formule voor XLOOKUP() als volgt:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Zoals verwacht retourneert die ook 2,5, de leeftijd van de eerste wit-met-grijze Nala.

Laten we de componenten van de bovenstaande formule verkennen. Hier betekent 1 TRUE, oftewel we zoeken naar de waarde TRUE in de opzoekarray die wordt vertegenwoordigd door de tweede component van de formule — (B2:B11=G2)*(C2:C11=G3).

Deze component bevat op zijn beurt twee vermenigvuldigers, die elk een bepaalde voorwaarde controleren: de eerste controleert of de naam overeenkomt met de gezochte naam, terwijl de tweede controleert of de kleur overeenkomt met de gezochte kleur. Laten we elke vermenigvuldiger in een aparte Excel-cel uitvoeren:

=B2:B11=G2

Het resultaat is:

=C2:C11=G3

Het resultaat is:

Laten we nu de hele tweede component van de formule uitvoeren:

=(B2:B11=G2)*(C2:C11=G3)

Het resultaat is:

Hoewel we twee waarden van 1 in het bovenstaande resultaat zien (wat twee TRUE-waarden betekent), retourneert de functie XLOOKUP() het item dat overeenkomt met de eerste match.

De aanpak met booleaanse expressies bij het uitvoeren van de functie XLOOKUP() voor meerdere criteria oogt veel complexer en minder intuïtief dan de benadering op basis van concatenatie. Dat zou nog erger worden als we meer dan twee zoekcriteria zouden gebruiken. Waarom kunnen we dan niet altijd concatenatie gebruiken?

De benadering met booleaanse expressies geeft ons veel meer flexibiliteit bij het gebruik van XLOOKUP() met meerdere criteria. Laten we naar de voordelen ten opzichte van de concatenatiebenadering kijken.

De FALSE-waarde controleren

In de bovenstaande formule controleerden we of de booleaanse expressie TRUE is, door de waarde 1 door te geven:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

In sommige gevallen moeten we misschien controleren of de booleaanse expressie juist FALSE is. Bijvoorbeeld: we willen de leeftijd vinden van de eerste kat in onze tabel die geen wit-met-grijze Nala is. We moeten onze formule dan als volgt aanpassen:

=XLOOKUP(0, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Het resultaat is 1, de leeftijd van de eerste kat die geen wit-met-grijze Nala is (precieser: het is een driekleurige Nala, maar geen wit-met-grijze).

Logische operatoren gebruiken

Met booleaanse expressies zijn we niet beperkt tot het controleren van gelijkheid. Stel dat we de kleur willen vinden van een kat die Nala heet en jonger is dan 2 jaar:

In dit geval is de formule voor XLOOKUP() als volgt:

=XLOOKUP(1, (B2:B11=G2)*(D2:D11<G3), C2:C11)

Het resultaat is Tricolor.

Ten minste één criterium halen

Tot nu toe controleerden we de overeenstemming met alle opgegeven criteria. In andere scenario’s moeten we misschien aan minstens één criterium voldoen.

Om te zien hoe dit werkt, gaan we terug naar onze initiële taak — de leeftijd vinden van een wit-met-grijze kat die Nala heet:

Dit keer willen we echter de leeftijd vinden van een kat die ofwel Nala heet of wit-met-grijs is. De XLOOKUP()-formule is in dit geval:

=XLOOKUP(1, (B2:B11=G2)+(C2:C11=G3), D2:D11)

Die retourneert 1, de leeftijd van een driekleurige Nala.

Ter herinnering: toen we zochten naar de leeftijd van een kat die zowel Nala heet als wit-met-grijs is, was de formule als volgt:

=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Het vervangen van * door + maakte het verschil.

Als je Excel’s volledige potentieel wilt ontdekken, overweeg dan het uitgebreide, praktijkgerichte en goed afgeronde vaardigheidstraject Excel Fundamentals.

Optionele parameters voor XLOOKUP() met meerdere criteria

Net als bij het reguliere gebruik van XLOOKUP() met één criterium, kunnen we extra opties van deze functie gebruiken wanneer we haar met meerdere criteria uitvoeren. Dit is waar de optionele parameters [if_not_found], [match_mode] en [search_mode] in beeld komen.

Laten we snel kijken naar voorbeelden van elk van deze parameters met XLOOKUP() en meerdere criteria. Voor de eenvoud passen we in elk voorbeeld de concatenatiebenadering toe.

Opgegeven tekst retourneren wanneer geen overeenkomst is gevonden

Hier zoeken we naar de leeftijd van een zwarte Nala — een niet-bestaande kat in onze tabel:

In dit geval hebben we aan de XLOOKUP()-formule de optionele parameter [if_not_found] toegevoegd, waaraan we de tekst hebben toegekend die moet worden geretourneerd als geen overeenkomst wordt gevonden:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, "No cat is found")

Inderdaad, de functie retourneerde de opgegeven tekst: No cat is found. Als we geen tekst hadden opgegeven, zou de functie #N/A hebben geretourneerd.

Een benaderende overeenkomst retourneren

Laten we zeggen dat we zoeken naar de leeftijd van een driekleurige Nala, maar we niet zeker weten of haar kleur in de tabel is geschreven als "Tricolor" of "Tricolour". Dan moeten we een benaderende, op jokers gebaseerde overeenkomst gebruiken en het argument [match_mode] met waarde 2 aan de functie XLOOKUP() doorgeven.

In de bovenstaande tabel gebruikten we een asterisk (*), die elk aantal tekens vertegenwoordigt, inclusief 0. Andere jokertekens zijn te vinden in de Microsoft Office documentatie.

De XLOOKUP()-formule is hier:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , 2)

En die retourneert 1, de leeftijd van de eerste (en enige) driekleurige Nala in onze tabel.

Merk op dat argument 2 in de bovenstaande formule betekent dat we geïnteresseerd zijn in een benaderende overeenkomst, in plaats van te zoeken naar de leeftijd van een kat Nala met de kleur "Tricolo*r." In ons geval ving de asterisk het woord "Tricolor" op, maar hij zou hetzelfde doen bij de woorden "Tricolour" of bijvoorbeeld "Tricolooor."

Een omgekeerde zoekopdracht uitvoeren

Laten we dezelfde oefening doen als in het begin — de leeftijd vinden van een wit-met-grijze Nala — maar dit keer beginnen we onze zoekopdracht vanaf het laatste item.

Daarvoor moeten we aan de functie XLOOKUP() de optionele parameter [search_mode] toevoegen met waarde -1, zoals hieronder:

=XLOOKUP(G2&G3, B2:B11&C2:C11, D2:D11, , , -1)

De functie retourneert 7, de leeftijd van de eerste wit-met-grijze Nala vanaf het einde van de opzoekarray.

XLOOKUP() vs. INDEX() en MATCH()

In oudere versies van Excel moeten we, om functionaliteit te reproduceren die vergelijkbaar is met het gebruik van de functie XLOOKUP() met meerdere criteria, de functies INDEX() en MATCH() combineren. Zonder in te gaan op de syntaxis van beide functies, kijken we hoe we de leeftijd van de eerste wit-met-grijze Nala kunnen vinden met de oude methode INDEX() + MATCH() en met de nieuwe functie XLOOKUP() (voor de celverwijzingen kunnen we de vorige tabel raadplegen):

=INDEX(D2:D11, MATCH(1, (G2=B2:B11)*(G3=C2:C11), 0))
=XLOOKUP(1, (B2:B11=G2)*(C2:C11=G3), D2:D11)

Hoewel de argumenten enigszins vergelijkbaar lijken, noemen we de belangrijkste voordelen van het uitvoeren van de functie XLOOKUP() ten opzichte van de oude methode:

• Eén functie is voldoende voor deze bewerking — functies combineren is niet nodig.

• De mogelijkheid om de concatenatiebenadering toe te passen wanneer dat kan.

• De mogelijkheid om de optionele parameters te gebruiken (de functie MATCH() mist deze).