XLOOKUP(): Excels meest veelzijdige zoekfunctie

Als je ooit VLOOKUP() hebt gebruikt en vastliep omdat je naar links moest zoeken, lost XLOOKUP() dat probleem op. Het is Excels nieuwere zoekfunctie. Het kan VLOOKUP(), HLOOKUP() en zelfs de meeste INDEX()- en MATCH()-formules vervangen met één simpele formule.

XLOOKUP() kan in elke richting zoeken, meer dan één kolom tegelijk ophalen en een aangepast bericht tonen wanneer een waarde ontbreekt. 

In deze gids lopen we eerst de syntaxis door. Daarna bouwen we stap voor stap echte formules, met één eenvoudig datasetje de hele tijd. We behandelen ook exacte en benaderende overeenkomsten, hoe je meer dan één kolom retourneert, hoe je fouten afhandelt en hoe XLOOKUP() zich verhoudt tot VLOOKUP() en INDEX()/MATCH(). 

XLOOKUP() gebruiken in Excel

Laten we een echte XLOOKUP()-formule vanaf nul opbouwen. 

Onze dataset heeft vijf producten, elk met een ID, een naam, een categorie en een prijs, van kolom A tot en met D, met kopteksten in rij 1.

Dataset voor XLOOKUP(). Afbeelding door auteur.

Stel dat we de prijs van "P104" willen vinden. Laten we stap voor stap bekijken hoe je dat doet.

Eén waarde opzoeken

Klik in een lege cel waar je het resultaat wilt zien. Ik gebruik F2. Typ vervolgens de volgende formule:

=XLOOKUP("P104", A2:A6, D2:D6)

In deze formule: 

• "P104" is de lookup_value, het ding waar we naar zochten

• A2:A6 is de lookup_array, het bereik waarin Excel heeft gezocht

• D2:D6 is de return_array, het bereik waaruit Excel het antwoord heeft gehaald

Excel zoekt in kolom A, “Product ID”, naar "P104", vindt het in rij 5 en retourneert de bijbehorende waarde uit kolom D: $45.

In plaats van direct "P104" te typen, kun je ook naar een cel verwijzen, zodat je de zoekwaarde kunt wijzigen zonder de formule zelf te bewerken. In ons geval bevat cel A5 "P104", dus wordt de formule:

=XLOOKUP(A5, A2:A6, D2:D6)

XLOOKUP() gebruiken in Excel. Afbeelding door auteur.

Nu kun je elk product-ID in A5 typen en de prijs wordt automatisch bijgewerkt.

Een waarde uit een andere kolom retourneren

In deze stap zie je het echte verschil tussen XLOOKUP() en VLOOKUP(). Je zit niet vast aan het retourneren van een waarde uit een kolom rechts van je zoekkolom.

Stel dat je in plaats van de prijs de categorie voor "P102" wilt. Zelfde opzet, wijs de return_array gewoon naar kolom C, “Category”:

=XLOOKUP(A3, A2:A6, C2:C6)

Hier geeft A3 Home terug. Als je het wijzigt naar A5, wordt het Electronics. Wijzig op dezelfde manier de return_array opnieuw om de productnaam, prijs of een andere kolom op te halen, allemaal met dezelfde lookup_value en lookup_array.

XLOOKUP() retourneert een waarde uit een andere kolom. Afbeelding door auteur.

Met VLOOKUP() tel je kolommen vanaf je zoekkolom en pas je een kolomindexnummer aan telkens wanneer je een ander resultaat wilt. Met XLOOKUP() wijs je gewoon direct de kolom aan die je wilt. 

De formule kopiëren over meerdere rijen

Tot nu toe hebben we één waarde tegelijk opgezocht. Maar de meeste echte spreadsheets moeten veel waarden tegelijk opzoeken, bijvoorbeeld een volledige lijst met order-ID’s die elk een bijpassende prijs nodig hebben.

Zo richt je dat in: Kolom A, beginnend bij A2, bevat al onze lijst met product-ID’s. We willen de bijbehorende prijzen ernaast laten verschijnen, in kolom F.

Klik daarvoor op F2 en typ de formule, met A2 als zoekwaarde zoals dit:

=XLOOKUP(A2, $A$2:$A$6, $D$2:$D$6)

Klik nu opnieuw op F2 en sleep vervolgens de vulgreep (het kleine vierkantje rechtsonder in de cel) omlaag door de rest van je rijen.

Excel kopieert de formule naar beneden en elke rij retourneert nu de juiste prijs voor zijn eigen product-ID. De ID in A2 krijgt zijn prijs in F2, de ID in A3 in F3, enzovoort.

Kopieer de formule naar andere cellen. Afbeelding door auteur.

Let op de dollartekens rond A2:A6 en D2:D6. Dit heet een absolute verwijzing. Het vergrendelt het bereik, zodat het niet verschuift wanneer je de formule naar beneden kopieert. 

Dat is hier belangrijk, omdat elke rij dezelfde productlijst moet doorzoeken, alleen telkens met een andere zoekwaarde.

Exacte en benaderende overeenkomsten gebruiken in XLOOKUP()

Standaard zoekt XLOOKUP() naar een exacte overeenkomst. Als die niet wordt gevonden, geeft het een fout, tenzij je het if_not_found argument hebt ingesteld zoals we eerder bespraken. 

Maar XLOOKUP() kan ook de dichtstbijzijnde overeenkomst vinden met het argument match_mode. 

Exacte overeenkomst (standaard)

Hiervoor hoef je niets extra’s te typen. Elke formule die we tot nu toe hebben gebouwd, gebruikt al een exacte overeenkomst, omdat dat het standaardgedrag van XLOOKUP() is.

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6)

Excel zoekt naar de exacte waarde in A2 en retourneert alleen een resultaat als het die precieze overeenkomst ergens in het zoekbereik vindt.

Als je het expliciet wilt uitschrijven, voeg dan een nul toe als vierde argument:

=XLOOKUP(A2, $A$2:$A$6, $B$2:$B$6, , 0)

XLOOKUP() retourneert een exacte overeenkomst. Afbeelding door auteur.

Zie je de lege plek vóór de 0? Daarmee sla je if_not_found over, omdat we dat hier leeg laten, en ga je meteen naar match_mode. Meestal kun je dit argument helemaal weglaten en Excel zelf laten terugvallen op exact zoeken.

Benaderende overeenkomst

Stel nu dat je gegevens niet voor elke mogelijke zoekwaarde een rij hebben. Denk aan een commissiestructuur waarin verkopers verschillende percentages verdienen afhankelijk van de range waarin hun totale verkoop valt. Je hebt geen rij voor elk dollarbedrag, alleen het startpunt van elke range.

Hier helpt een benaderende overeenkomst. 

Zet match_mode op -1 en XLOOKUP() vindt de grootste waarde die kleiner dan of gelijk is aan je zoekwaarde.

=XLOOKUP(C2, $A$2:$A$5, $B$2:$B$5, , -1)

Stel dat je zoekbereik verkooptresholds van $0, $5.000 en $10.000 bevat, elk gekoppeld aan een ander commissiepercentage. 

Een vertegenwoordiger met $7.500 aan verkoop komt niet exact overeen met een rij. Met match_mode op -1 vindt XLOOKUP() $5.000, de grootste drempel die $7.500 niet overschrijdt, en retourneert het commissiepercentage van die rij.

Zet match_mode in plaats daarvan op 1 en XLOOKUP() draait de logica om. Het vindt de kleinste waarde die groter dan of gelijk is aan je zoekwaarde. Dit is handig voor dingen als verzendgewichtsschalen, waarbij je afrondt naar de volgende klasse in plaats van naar beneden.

XLOOKUP() retourneert een benaderende overeenkomst. Afbeelding door auteur.

Let op: benaderende overeenkomst gaat ervan uit dat je zoekbereik gesorteerd is, oplopend voor -1, aflopend voor 1. Als het niet correct gesorteerd is, kan XLOOKUP() het verkeerde resultaat retourneren zonder een foutmelding te geven, wat het nog lastiger maakt. Controleer dus altijd je sorteervolgorde voordat je hierop vertrouwt.

Waarden links opzoeken met XLOOKUP()

VLOOKUP() doorzoekt alleen de meest linkse kolom van je bereik en retourneert een waarde uit een kolom rechts daarvan; het kan niet andersom. Het derde argument is geen bereik maar een kolomindex vanaf de linkerzijde. 

Er is geen manier om te zeggen "ga één kolom naar links", dus als de waarde die je nodig hebt links van je zoekkolom staat, zit je vast aan het herschikken van je data of het toevoegen van een hulpkolom om het zoeken te laten werken.

XLOOKUP() heeft deze beperking niet, omdat je aparte bereiken aanwijst voor het zoeken en het retourneren.

Neem onze productlijst: Product ID staat in kolom A, Product Name in kolom B. 

Stel dat je een productnaam hebt en het ID wilt vinden; het omgekeerde van wat VLOOKUP() kan.

Voer in C9 Bluetooth Speaker in (een naam die in de lijst voorkomt). Voer in E2 in:

=XLOOKUP(C9, $B$2:$B$6, $A$2:$A$6)

XLOOKUP() zoekt in kolom B naar "Bluetooth Speaker", vindt het in rij 5 en retourneert "P104" uit kolom A, ook al staat kolom A links van kolom B. 

XLOOKUP() zoekt ook naar links, in tegenstelling tot VLOOKUP(). Afbeelding door auteur.

Meerdere kolommen retourneren met XLOOKUP()

XLOOKUP() kan meerdere kolommen tegelijk retourneren, in één formule, met een functie genaamd dynamische arrays. Wijs daarvoor return_array aan als een bereik in plaats van één kolom, en XLOOKUP() laat de hele overeenkomende rij “overlopen” in zoveel cellen als nodig is. 

Laten we het proberen met onze productlijst. 

In C9 typ ik een product-ID, bijvoorbeeld "P104". In E2 voer ik in:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6)

Let op: de return_array is hier B2:D6, drie kolommen breed, met Product Name, Category en Price. Zodra je op Enter drukt, vindt XLOOKUP() "P104" in kolom A en retourneert Bluetooth Speaker, Electronics en $45 in één keer, die “overlopen” naar E9, F9 en G9.

XLOOKUP() retourneert meerdere kolommen met één formule. Afbeelding door auteur.

Dit is wat een spill-bereik betekent. E9 bevat de eigenlijke formule en F9 en G9 vullen zichzelf in, zonder eigen formules. 

Klik op F9 en de formulebalk toont een uitgegrijsde versie van de oorspronkelijke formule, niet iets dat daar direct is getypt. Probeer in F9 te typen terwijl de spill van E9 actief is en Excel geeft een spill-fout. 

We behandelen hoe je dat oplost in de sectie Veelvoorkomende fouten.

Omgaan met ontbrekende waarden in XLOOKUP()

Standaard geeft XLOOKUP() een #N/A-fout wanneer er geen overeenkomst is. Het if_not_found argument kan dit oplossen door te tonen wat jij wilt in plaats van de standaardfout.  

Stel dat ik in C9 een product-ID typ die niet in mijn data bestaat, zoals "P999".

Een aangepast bericht retourneren

In G2 voer ik in:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "Not Found")

Omdat "P999" niet in kolom A bestaat, slaat XLOOKUP() de fout volledig over en retourneert "Not Found". Verander de tekst naar wat je wilt, zoals "Check Product ID" of "No Match".

Toon een aangepast bericht in plaats van de standaardfout. Afbeelding door auteur.

Een lege cel retourneren

Soms wil je een lege cel zonder tekst. Gebruik hiervoor twee aanhalingstekens met niets ertussen, zoals dit:

=XLOOKUP(C9, $A$2:$A$6, $B$2:$D$6, "")

Dit kun je gebruiken wanneer je deze formule in een rapport gebruikt waar een losse "Not Found" rommelig zou staan naast een kolom met echte waarden.

Toon een lege cel in plaats van de standaardfout. Afbeelding door auteur.

Het als een echte fout laten staan

Als je if_not_found helemaal overslaat, valt XLOOKUP() terug op #N/A. Dat is niet altijd slecht. Als je een formule voor jezelf bouwt en een typefout of ontbrekende invoer meteen wilt zien, is #N/A handiger dan een stille lege cel die het probleem verbergt.

Let op: if_not_found vangt alleen ontbrekende overeenkomsten. Als je lookup_array en return_array bijvoorbeeld verschillende afmetingen hebben, geeft XLOOKUP() nog steeds een #VALUE!-fout, en if_not_found stopt die niet. We behandelen dat specifieke geval verderop in de sectie Veelvoorkomende fouten.

Geavanceerde XLOOKUP()-functies

Laten we de geavanceerde mogelijkheden van XLOOKUP() bekijken

Zoeken van onder naar boven

Zet search_mode op -1 en XLOOKUP() scant je bereik van onder naar boven in plaats van van boven naar beneden. Gebruik dit wanneer je data dubbele waarden bevat en je de meest recente invoer wilt, niet de eerste.

Stel dat ik in mijn dataset drie dubbele klant-ID’s heb. In cel E2 voer ik de standaardformule in die de benadering van boven naar beneden gebruikt: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6)

Dit wordt vanaf het begin van de tabel berekend en geeft het resultaat van de eerste match weer. 

Maar als ik van onderaf wil zoeken, gebruik ik deze formule: 

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, -1)

In deze formule vertelt -1 Excel om te beginnen met zoeken vanaf de onderkant van de dataset. Dit retourneert 340, omdat dat de eerste overeenkomende waarde vanaf de onderkant is.  

XLOOKUP() scant data van onder naar boven. Afbeelding door auteur.

Binaire zoekmodus

Zet search_mode op 2 en XLOOKUP() schakelt van rij-voor-rij controleren naar een binaire zoekactie. 

Zo werkt het: 

• Het kijkt eerst naar het middelste item in je bereik. 

• Als dat geen match is, gooit het de helft weg waar de waarde niet kan staan en kijkt dan naar het midden van wat overblijft. 

• Het blijft dit doen, de data telkens halveren, totdat het de match vindt. 

• Zet search_mode op -2 en het werkt hetzelfde, maar begint bij het laatste item in plaats van het eerste.

Voer hier in E2 in:

=XLOOKUP(C9, $A$2:$A$6, $C$2:$C$6, , 0, 2)

Deze formule retourneert in plaats daarvan 200, een volledig andere rij.

Binaire zoekmodus geeft het verkeerde resultaat. Afbeelding door auteur.

Want binaire zoekactie werkt alleen als je lookup_array gesorteerd is. Omdat onze kolom Customer ID niet gesorteerd is — het gaat C100, C101, C100, C102, C100 — gooit de binaire zoekactie de verkeerde helft weg en geeft een verkeerde overeenkomst. 

Laten we nu zien wat er gebeurt wanneer de data wél gesorteerd is. 

Sorteer hetzelfde logboek op Customer ID, oplopend (alle drie C100 aan het begin) en voer dezelfde formule opnieuw uit. 

Binaire zoekmodus werkt met gesorteerde data. Afbeelding door auteur.

Deze keer geeft E2 120 terug, overeenkomend met wat een normale zoekactie zou geven. Dat is het verschil. Binaire zoekactie slaat alleen data over die het veilig kan uitsluiten, en dat geldt alleen wanneer de data vooraf gesorteerd is. 

Geneste XLOOKUP()-formules

Nest één XLOOKUP() in een andere wanneer het resultaat van een zoekactie moet doorstromen naar een tweede zoekactie, in plaats van naar een vast bereik.

Stel dat je een prijs wilt vinden, maar welke kolom je gebruikt hangt af van een aparte zoekactie, bijvoorbeeld de prijscategorie van een klant.

=XLOOKUP(C9, $A$2:$A$6, XLOOKUP(D9, $F$2:$F$4, $G$2:$I$4))

In deze formule bepaalt de binnenste XLOOKUP() eerst welk kolombereik moet worden gebruikt op basis van D9, waarna de buitenste XLOOKUP() dat resultaat gebruikt als zijn return_array. 

Geneste XLOOKUP(). Afbeelding door auteur.

XLOOKUP() vs. VLOOKUP()

We hebben uitgebreid behandeld hoe XLOOKUP() werkt. Hier is de vergelijking met VLOOKUP().

Er zijn twee dingen die een korte opmerking waard zijn: 

• VLOOKUP() gebruikt standaard benaderend zoeken. Als je het vierde argument vergeet, toont het geen fout; het geeft stilletjes de verkeerde waarde terug.

• Als je een kolom invoegt in een VLOOKUP()-blad, beginnen formules ongemerkt de verkeerde waarde op te halen. XLOOKUP() voorkomt dit door direct naar bereiken te verwijzen in plaats van kolommen te tellen.

Geen van dit maakt VLOOKUP() overbodig. Het werkt nog steeds prima voor eenvoudige, statische sheets. Maar voor alles wat je vanaf nu bouwt, is XLOOKUP() de veiligere standaard.

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

Voordat XLOOKUP() bestond, waren INDEX() en MATCH() samen de standaard manier om de beperkingen van VLOOKUP() te omzeilen. Het gebruikt twee functies in plaats van één. 

• MATCH() vindt de positie van je zoekwaarde. 

• INDEX() gebruikt die positie vervolgens om het resultaat op te halen van waar je het aanwijst: links of rechts, dat maakt niet uit.

=INDEX(D2:D6, MATCH(C9, A2:A6, 0))

Vergelijk dat met de XLOOKUP()-versie die we door dit artikel heen hebben gebruikt:

=XLOOKUP(C9, A2:A6, D2:D6)

XLOOKUP() werkt alleen in Microsoft 365 en Excel 2021 en nieuwer, zoals we eerder bespraken. Als je werkmap in Excel 2019 of 2016 moet openen, of je deelt met mensen op oudere versies, dan werken INDEX() en MATCH() nog overal.

Er is ook een kleiner geval. 

INDEX() en MATCH() scheiden “vind de positie” van “retourneer de waarde”, wat helpt in sommige complexe geneste formules. Dit komt zelden voor, dus de meeste lezers hebben het niet nodig.

Buiten deze twee gevallen is XLOOKUP() de betere keuze voor een nieuwe werkmap. 

Veelvoorkomende XLOOKUP()-fouten en hoe je ze oplost

Hier is een snelle referentie van de problemen die je het vaakst zult tegenkomen en hoe je elk oplost.

#N/A-fouten

Dit is de standaardreactie van XLOOKUP() wanneer er geen overeenkomst is. Ik liep hier eerder tegenaan, toen mijn lookup_value en lookup_array niet overeenkwamen en XLOOKUP() niets had om mee te matchen in kolom A.

Oplossing: Controleer of je lookup_value daadwerkelijk voorkomt in je lookup_array, op dezelfde manier gespeld en opgemaakt. Als ontbrekende overeenkomsten verwacht en normaal zijn, gebruik dan het argument if_not_found om #N/A te vervangen door iets nuttigers, zoals "Not Found" of een lege cel, zoals we eerder hebben behandeld. 

Onjuist zoekbereik

Dit gebeurt wanneer je lookup_array en return_array niet goed op elkaar aansluiten: verkeerde rijen, verkeerde kolommen of bereiken die niet even groot zijn. 

Afhankelijk van welke versie van Excel of Sheets je gebruikt, kan dit verschijnen als een #VALUE!-fout, of soms als #N/A met een apart bericht dat het echte probleem uitlegt. Laat je niet misleiden door #N/A om te denken dat het om een ontbrekende match gaat. 

Oplossing: Zorg dat lookup_array en return_array hetzelfde aantal rijen of kolommen omvatten. Als lookup_array A2:A5 is, moet return_array ook de rijen 2 t/m 5 beslaan, niet rijen 4 t/m 5 of een ander niet-passend bereik.

Datatypemismatches

VLOOKUP() kan een fout geven omdat het een getal verwachtte maar tekst kreeg. XLOOKUP() kan een vergelijkbaar probleem hebben. Als je lookup_value een getal is, maar je lookup_array diezelfde waarden als tekst opslaat, of andersom, dan vindt XLOOKUP() geen overeenkomst, ook al lijken de waarden op het scherm identiek.

Oplossing: Controleer of je lookup_value en lookup_array hetzelfde datatype gebruiken. Als tekst opgeslagen getallen staan meestal links uitgelijnd in plaats van rechts. Wikkel je lookup_value in VALUE() om tekst naar een getal te converteren, of gebruik TEXT() om het omgekeerde te doen, afhankelijk van de richting van de mismatch.

Spill-fouten

Dit gebeurt wanneer je return_array meer dan één kolom omvat, maar de cellen die XLOOKUP() nodig heeft niet leeg zijn. Excel heeft vrije ruimte naast je formulecel nodig om de resultaten te laten overlopen. Als daar al iets staat, faalt de hele formule.

Oplossing: Maak de cellen leeg waar de resultaten moeten komen en voer dan je formule in. Als je iets in die cellen nodig hebt, verplaats je XLOOKUP()-formule dan naar een andere plek met vrije ruimte eromheen.

Tot slot 

Je weet nu hoe je XLOOKUP() gebruikt voor enkele waarden, meerdere kolommen, ontbrekende data en zelfs geneste zoekacties over twee bereiken. Dat is een hoop terrein.

Maak XLOOKUP() voortaan je standaard voor elke nieuwe werkmap. Het is flexibeler dan VLOOKUP(), leesbaarder dan INDEX/MATCH() en je hoeft niet elke formule in IFERROR() te wikkelen.

Wil je dieper gaan, dan is het ook de moeite waard om INDEX() en MATCH() op zichzelf te leren.