XLOOKUP: de discussie is voorbij

XLOOKUP is de nieuwste zoekfunctie van Excel. XLOOKUP combineert INDEX&MATCH, VLOOKUP en HLOOKUP en vormt daarmee het nieuwste wapen in het arsenaal van zoekformules in Excel. De belangrijkste vraag is dan ook: waarom zou je XLOOKUP gebruiken?

Waarom zou je XLOOKUP gebruiken?

Om deze vraag te beantwoorden is het goed om eerst even te kijken naar de bestaande zoekfuncties, en dan met name naar VLOOKUP (VERT.ZOEKEN). Laten we beginnen met de syntax van de website van Microsoft:

Selecteer een cel.
Typ =VLOOKUP( en selecteer de waarde die opgezocht moet worden.
Typ een komma (,) OF puntkomma (;) en selecteer het bereik of de tabel waarin gezocht moet worden.
Typ een komma (,) OF puntkomma (;) en het nummer van de kolom waar de op te zoeken waarde zicht bevindt.
Typ ,FALSE) om een exacte match te vinden.
Klik op Enter.

Vrij eenvoudig, op punt 5 na. Als je namelijk ‘TRUE’ typt, in plaats van ‘FALSE’, dan krijg je als resultaat een zogenaamde ‘approximate lookup’, ofwel een resultaat ‘bij benadering’. Dit is een zeer specifieke instelling die alleen werkt in het geval van een geordende tabel. Doorgaans is de uitkomst ervan ongewenst, omdat je in de meeste gevallen op zoek zult zijn naar een exacte uitkomst. Het probleem is echter, dat een VLOOKUP functie altijd een Approximate Lookup als resultaat zal geven wanneer je het laatste argument (TRUE of FALSE) eruit weglaat.

Ook het kolomnummer als index kan een probleem vormen. Als je een kolom toevoegt aan of uitsluit van je originele selectie, dan kan de functie namelijk in een verkeerde kolom gaan zoeken. En als je je tabel vergroot breidt de VLOOKUP functie niet automatisch ook het zoekbereik uit.

Tekortkomingen van oudere zoekfuncties

Het is duidelijk dat de VLOOKUP functie nogal wat tekortkomingen heeft. Een geschikt alternatief is dan ook de combinatie van INDEX en MATCH (VERGELIJKEN). Dit heeft een vergelijkbare tekortkoming als VERT.ZOEKEN, omdat het eveneens een Approximate Lookup heeft die is ingesteld om “de grootste waarde te vinden die kleiner is dan of gelijk is aan de gezochte waarde”. De combinatie INDEX & MATCH werkt beter met betrekking tot wijzigingen in de dataset, maar gebruiker ervaren deze combinatie helaas vaak als een gecompliceerde methode:

“=INDEX(C3:E11,MATCH(H2,B3:B11,0),2)”

Advantages of XLOOKUP

Dus dit brengt ons bij de nieuwste zoekfunctie: XLOOKUP. Dit zijn de voordelen:

Het standaard resultaat is een exacte match; over het algemeen zul je daar naar op zoek zijn. Als er geen exacte match gevonden wordt, dan kun je kiezen voor een hogere of lagere waarde indien je dat wilt.
De syntax is heel eenvoudig “=XLOOKUP(lookup_value, lookup_array, return_array)”. Dit is precies zoals je het wilt en het werkt als een tierelier.
In tegenstelling tot VLOOKUP (en HLOOKUP) kan XLOOKUP een array met meerdere waarden opleveren. Dus de functie maakt je leven, net als de meeste nieuwere Excel functies (dynamische arrays), een stuk makkelijker: als je 2 waarden nodig hebt kun je deze vinden met slechts een enkele functie.
Je kunt ook een “if not found” waarde toevoegen. Deze levert een tekstje of een waarde op in het geval dat de functie geen resultaten oplevert.
Er is een [search_mode] optie die je laat kiezen hoe je wilt zoeken. Dit is niet belangrijk voor kleinere modellen, maar als je te maken hebt met grote hoeveelheden data, dan kan het verschil tussen van boven naar beneden of van beneden naar boven zoeken een enorm verschil maken.

Het is duidelijk dat XLOOKUP verkozen moet worden boven alle oudere zoekfuncties in Excel: de functie presteert goed, is zeer gebruiksvriendelijk en bespaart simpelweg tijd en moeite.

Syntax van XLOOKUP

Het is duidelijk dat XLOOKUP verkozen moet worden boven alle oudere zoekfuncties in Excel: de functie presteert goed, is zeer gebruiksvriendelijk en bespaart simpelweg tijd en moeite.

Dit is de syntax van de XLOOKUP functie:

Selecteer een cel.
Typ =XLOOKUP( en selecteer de waarde die opgezocht moet worden.
Typ een komma (,) OF puntkomma (;) en selecteer de kolom/het bereik waarin gezocht moet worden.
Typ een komma (,) OF puntkomma (;) en selecteer de kolom/het bereik waar de op te zoeken waarde zich bevindt.
Klik op Enter.

N.B. INDEX & MATCH presteren nog wel beter in gevallen waarin je de “MATCH” -waarde meerdere keren gaat hergebruiken, omdat je de waarde slechts één keer hoeft op te zoeken en deze vervolgens op een willekeurig aantal formules en berekeningen kunt toepassen.