M365Con 2027 – gratis online conferentie · 11–22 Jan 2027 · 120 sessions→ m365con.net

Blog · Mirko Peters

XLOOKUP in Excel: syntaxis, voorbeelden en waarom het beter is dan VLOOKUP

By · · Updated

XLOOKUP in Excel uitgelegd: de syntaxis, elk argument, voorbeelden voor exacte, jokertekens- en laatste overeenkomsten, meerdere kolommen, en hoe het verschilt van VLOOKUP.

Automatically translated with AI from the original post. Read the English original

XLOOKUP in Excel zoekt een waarde in het ene bereik op en haalt het bijbehorende item uit een ander bereik op: =XLOOKUP(lookup_value, lookup_array, return_array). Typ een medewerker-ID, wijs de ID-kolom aan, wijs de naamkolom aan, en je krijgt de naam. Dit artikel is voor Excel-gebruikers die uit gewoonte nog VLOOKUP schrijven, of die net XLOOKUP zagen in het bestand van een collega. Je krijgt de syntaxis, de zes argumenten, uitgewerkte voorbeelden voor de situaties die op het werk voorkomen, de vergelijking met VLOOKUP en een overzicht van de fouten waar je waarschijnlijk tegenaan loopt.

Belangrijkste punten

  • XLOOKUP heeft een zoekwaarde, een zoekbereik en een retourbereik nodig: =XLOOKUP(lookup_value, lookup_array, return_array). Het zoekt de eerste overeenkomst en geeft het item uit het retourbereik terug.
  • Het kan links van de zoekkolom zoeken en gaat standaard uit van een exacte overeenkomst. Twee dingen die VLOOKUP niet kan, of standaard niet doet.
  • Het vierde argument, if_not_found, vervangt de #N/A-fout door tekst die jij kiest. Zonder dit argument geeft een zoekopdracht zonder overeenkomst #N/A terug.
  • match_mode 2 schakelt jokertekens in, -1 en 1 zoeken naar het eerstvolgende kleinere of grotere item, en search_mode -1 doorzoekt van het laatste item naar het eerste.
  • De pagina van Microsoft vermeldt XLOOKUP voor Microsoft 365, 2021 en 2024, en de opmerkingen over Excel 2016 en 2019 spreken elkaar tegen. Test gedeelde bestanden dus voordat je erop vertrouwt.

De syntaxis en opties hieronder volgen de pagina van Microsoft over de XLOOKUP-functie. Waar een patroon niet op die pagina staat, zegt de tekst dat. Het is een functie voor Excel, en het is de functie die Microsoft zelf voorstelt als de betere keuze wanneer je de helppagina van VLOOKUP opent.

De syntaxis en de zes argumenten

De volledige formule ziet er zo uit:

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

De eerste drie argumenten zijn verplicht. De laatste drie zijn optioneel.

  • lookup_value is wat je zoekt, bijvoorbeeld een ID in cel D2. Laat je het weg, dan geeft XLOOKUP de lege cellen terug die het in de lookup_array vindt.
  • lookup_array is het bereik of de matrix waarin wordt gezocht, zoals A2:A100.
  • return_array is het bereik of de matrix waaruit wordt teruggegeven, zoals B2:B100.
  • if_not_found is de tekst die je toont als er geen geldige overeenkomst is. Laat je het weg, dan krijg je #N/A.
  • match_mode bepaalt hoe er wordt gematcht. 0 is een exacte overeenkomst en de standaard. -1 betekent een exacte overeenkomst of, als die er niet is, het eerstvolgende kleinere item. 1 betekent een exacte overeenkomst of het eerstvolgende grotere item. 2 is een jokerteken-overeenkomst, waarbij *, ? en ~ een speciale betekenis hebben.
  • search_mode bepaalt de zoekrichting. 1 zoekt van het eerste item naar het laatste en is de standaard. -1 zoekt van het laatste naar het eerste. 2 en -2 zijn binaire zoekopdrachten op gegevens die oplopend of aflopend zijn gesorteerd. Microsoft waarschuwt dat ongesorteerde gegevens bij de binaire modi ongeldige resultaten opleveren.

Houd de zoekmatrix en de retourmatrix even groot. Als de zoekmatrix rijen 2 tot en met 100 beslaat, moet de retourmatrix ook rijen 2 tot en met 100 beslaan. Microsoft's pagina zegt dit niet expliciet als regel, maar niet-overeenkomende bereiken zijn de gebruikelijke reden dat een zoekopdracht de verkeerde rij teruggeeft of een fout geeft.

Basiszoekopdrachten en het argument voor niet gevonden

Stel dat de werknemers-ID's in A2:A100 staan en de namen in B2:B100. Je typt het ID dat je zoekt in D2.

  1. Klik op de cel waarin het resultaat moet verschijnen, bijvoorbeeld E2.
  2. Voer =XLOOKUP(D2,A2:A100,B2:B100) in.
  3. Druk op Enter.

Je krijgt de naam die bij het ID hoort. XLOOKUP geeft het resultaat van de eerste overeenkomst die het vindt. Staat het ID er niet tussen, dan krijg je #N/A.

Om iets vriendelijkers te tonen, voeg je het vierde argument toe:

=XLOOKUP(D2,A2:A100,B2:B100,"No record found")

Nu toont een ID dat niet in de lijst staat "No record found". Gebruik dit in werkbladen die anderen lezen. Eén kanttekening: zo'n melding kan een echt probleem verbergen. Als elke rij "No record found" zegt, is er iets mis met de bereiken, niet met de gegevens. Test daarom eerst met een ID waarvan je weet dat het bestaat.

Naar links zoeken

VLOOKUP kan geen waarde teruggeven uit een kolom links van de zoekkolom. XLOOKUP kan dat wel, omdat de zoek- en retourbereiken los van elkaar staan. Staat de naam in kolom C en wil je het ID terug dat in kolom A staat:

=XLOOKUP(F2,C2:C100,A2:A100)

Microsoft zegt dat rechtstreeks op de pagina: de zoekkolom mag links of rechts van de retourkolom staan.

Overeenkomstmodi: jokertekens en dichtstbijzijnde waarden

Overeenkomsten met jokertekens

Stel match_mode in op 2 om jokertekens te gebruiken. Het sterretje staat voor een willekeurig aantal tekens, het vraagteken voor precies één teken. Met een tilde zoek je naar een letterlijk sterretje of vraagteken.

=XLOOKUP("Jan*",B2:B100,A2:A100,"Not found",2)

Dit geeft de eerste invoer terug die begint met "Jan". Laat je de 2 weg, dan zoekt Excel naar de letterlijke tekst "Jan*" en krijg je je melding voor niet gevonden. Dat is de meest voorkomende fout met jokertekens. Maak jokertekens zo specifiek mogelijk, want XLOOKUP geeft alleen de eerste overeenkomst terug, en een ruim patroon kan iets raken wat je niet bedoelde.

Het eerstvolgende kleinere of grotere item

De modi -1 en 1 zijn bedoeld voor schalen: belastingtarieven, commissieschijven, prijsstaffels. Zet de drempelwaarden in A2:A6 en de tarieven in B2:B6, en zoek in E2 een inkomen op:

=XLOOKUP(E2,$A$2:$A$6,$B$2:$B$6,,-1)

Met -1 geeft Excel de exacte overeenkomst terug, of anders de eerstvolgende lagere drempelwaarde. De lege plek vóór de -1 slaat het argument voor niet gevonden over. Microsoft gebruikt in het eigen voorbeeld voor de benaderende overeenkomst 1 om een belastingtarief terug te geven bij de eerstvolgende hogere inkomenswaarde, dus kies de richting die bij je tabel past. Anders dan bij de oude benaderende VLOOKUP hoef je de drempelwaarden voor deze modi niet te sorteren. Alleen de binaire zoekmodi vereisen gesorteerde gegevens.

Van onderaf zoeken: de laatste overeenkomst

Komt een waarde meerdere keren voor, dan geeft XLOOKUP de eerste terug die het tegenkomt. Om de laatste te krijgen, zoek je vanaf het einde door search_mode op -1 in te stellen:

=XLOOKUP(D2,A2:A100,C2:C100,"Not found",0,-1)

Staan de bestellingen op datum gesorteerd, dan geeft dit de meest recente invoer voor een klant terug. De 0 houdt de overeenkomst exact. Dit is het patroon voor "laatste prijs", "laatste contact" en vergelijkbare vragen die met VLOOKUP lastig waren.

Meerdere kolommen tegelijk teruggeven

Microsoft's pagina vermeldt dat XLOOKUP, anders dan VLOOKUP, meerdere kolommen als één matrix kan teruggeven. Maak het retourbereik breder:

=XLOOKUP(D2,A2:A100,B2:D100)

De formule zet de overeenkomst uit kolom B in de cel en laat de waarden uit de kolommen C en D overlopen naar de cellen rechts ervan. Houd daar ruimte voor vrij. Staan er al gegevens in de cellen rechts, dan toont Excel een overloopfout in plaats van ze te overschrijven. Microsoft's voorbeeld haalt met één formule een werknemersnaam en een afdeling op.

Tweerichtingszoekopdrachten

Microsoft laat zien hoe je twee XLOOKUP-functies kunt nesten om een waarde te vinden op het kruispunt van een rij en een kolom. Het patroon van de pagina is:

=XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

De binnenste functie kiest de kolom die overeenkomt met de kop in C3. De buitenste functie zoekt vervolgens D2 in de rijkoppen en geeft de waarde uit die kolom terug. Microsoft vergelijkt dit met een combinatie van INDEX en MATCH. Heb je een rapport met producten in de eerste kolom en maanden in de koprij, dan is dit de vervanging voor een ingewikkelder formule.

Meerdere criteria

XLOOKUP neemt één opzoekwaarde, en de pagina van Microsoft bevat geen voorbeeld met meerdere criteria. Een veelgebruikt patroon is om Excel twee voorwaarden tegelijk te laten testen. Stel dat de categorie in A staat, de status in B en de prijs in C. Om de prijs te vinden voor de categorie in F2 en de status in G2:

=XLOOKUP(1,(A2:A100=F2)*(B2:B100=G2),C2:C100,"Niet gevonden")

Elke vergelijking geeft WAAR of ONWAAR terug. Door ze te vermenigvuldigen krijg je alleen in rijen waar beide waar zijn een 1, en XLOOKUP zoekt naar de eerste 1. Dit patroon werkt in de huidige versies van Microsoft 365, maar het staat niet op de functiepagina van Microsoft, dus test het op een kleine steekproef. Een hulpkolom die de twee velden samenvoegt is het oudere alternatief. Zet dan een scheidingsteken tussen de waarden, bijvoorbeeld =A2&"|"&B2, en zoek naar dezelfde samengevoegde tekst.

XLOOKUP en VLOOKUP naast elkaar

De VLOOKUP-pagina van Microsoft geeft de syntaxis als VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]) en zegt dat de opzoekwaarde in de eerste kolom van het bereik moet staan. Laat je range_lookup weg, dan voert VLOOKUP een benaderende overeenkomst uit, waarbij wordt aangenomen dat de eerste kolom gesorteerd is. Zet het op FALSE voor een exacte overeenkomst. Dezelfde pagina raadt aan XLOOKUP te proberen en zegt dat die in elke richting werkt en standaard exacte overeenkomsten teruggeeft.

Dezelfde klus in beide functies, met ID's in kolom A en namen in kolom B:

  • =VLOOKUP(D2,A2:B100,2,FALSE)
  • =XLOOKUP(D2,A2:A100,B2:B100)

De verschillen die in het dagelijks gebruik ertoe doen:

  • VLOOKUP verwijst naar het resultaat via het kolomnummer, dus als je een kolom in de tabel invoegt, verandert de betekenis van dat getal. XLOOKUP verwijst rechtstreeks naar het retourbereik.
  • VLOOKUP kan niet naar links kijken. XLOOKUP wel.
  • VLOOKUP gaat standaard uit van een benaderende overeenkomst. XLOOKUP standaard van een exacte.
  • XLOOKUP heeft een argument voor niet gevonden en de zoekrichting ingebouwd.

Je hoeft oude werkmappen niet te herschrijven. Als een VLOOKUP werkt, laat hem staan. Gebruik XLOOKUP voor nieuwe formules, en voor opzoekingen die naar links moeten kijken, de laatste overeenkomst willen of meerdere retourkolommen nodig hebben.

Welke Excel-versies XLOOKUP ondersteunen

Hier moet je oppassen. De pagina van Microsoft vermeldt XLOOKUP voor Excel voor Microsoft 365 op Windows en Mac, Excel 2024, Excel 2021 en de apps voor iPad, iPhone en Android. Een opmerking op de pagina zegt dat het niet beschikbaar is in Excel 2016 of 2019, maar wel kan verschijnen in werkmappen die in nieuwere versies zijn gemaakt, en de pagina vermeldt 2016 en 2019 ook in de regel van toepassing. Dat spreekt zichzelf tegen, dus beschouw Excel 2016 en 2019 niet als veilig. Excel voor het web staat op die pagina ook niet vermeld.

Als je een werkmap naar iemand stuurt die een oudere versie gebruikt, kan de formule een fout tonen waar die een waarde zou moeten tonen. Vraag welke versie je ontvangers gebruiken, en test bij bestanden die naar veel mensen gaan op de oudste versie die in gebruik is. Kun je niet testen, gebruik dan voor dat bestand een klassieke opzoekfunctie.

Veelgemaakte fouten en foutmeldingen

  • Het vergeten van match_mode 2 bij gebruik van jokertekens. De zoekopdracht zoekt dan naar de letterlijke tekens.
  • Een binaire search_mode (2 of -2) gebruiken op ongesorteerde gegevens. Volgens Microsoft zijn de resultaten dan ongeldig.
  • Opzoek- en retourbereiken van verschillende lengte. Maak ze even lang.
  • Verborgen spaties en getallen die als tekst zijn opgeslagen. "100" en 100 zijn verschillend, dus de opzoeking mislukt en je ziet #N/B. Maak de gegevens schoon met TRIM, of zet de getallen om.
  • Problemen verbergen met een vriendelijke melding voor niet gevonden. Test één bekende waarde voordat je erop vertrouwt.
  • Het retourbereik laten verwijzen naar cellen waar het resultaat naartoe moet morsen, en die cellen bezet laten.
  • Verwijzingen naar volledige kolommen op zeer grote werkbladen. Gebruik een begrensd bereik of een Excel-tabel, zodat Excel minder cellen doorloopt.

Gerelateerde onderwerpen om te verkennen

  • XLOOKUP versus INDEX en MATCH. Beide kunnen naar links zoeken, en de combinatie INDEX en MATCH werkt ook in oudere Excel-versies. Lees er meer over als je bestanden deelt met mensen die Excel 2016 of 2019 gebruiken.
  • Meerdere resultaten. XLOOKUP geeft alleen de eerste overeenkomst terug. Wil je alle overeenkomsten zien, kijk dan naar de functie FILTER.
  • XLOOKUP met Excel-tabellen. Gestructureerde verwijzingen zorgen ervoor dat bereiken meegroeien met de gegevens, en ze zijn goed leesbaar in formules.
  • Gegevens uit Power Query. Nadat je gegevens in een tabel hebt geladen, kan XLOOKUP erin zoeken zoals in elke andere tabel. Vernieuw de query eerst en controleer daarna de resultaten.
  • Excel voor het web. De XLOOKUP-pagina van Microsoft vermeldt dit niet. Controleer daarom in je eigen omgeving of de functie voor je collega's werkt voordat je een bestand deelt.

Volgende stap

Neem één VLOOKUP uit je huidige werkmap en herschrijf die als XLOOKUP. Voeg een melding voor niet-gevonden waarden toe en vergelijk de twee resultaten met een paar ID's die je kent. Als beide hetzelfde opleveren, heb je een patroon dat je kunt hergebruiken voor elke nieuwe XLOOKUP in Excel.

Bronnen

Entities in this article

Microsoft Excel Excel for the web

Related articles

RSS