Bij de keuze tussen XLOOKUP en VLOOKUP geldt: gebruik XLOOKUP als iedereen die het bestand opent Excel voor Microsoft 365, Excel 2021 of nieuwer, of Excel voor het web heeft. Het doet alles wat VLOOKUP doet met minder fouten: standaard een exacte overeenkomst, het kan naar links zoeken en het gaat niet kapot als je een kolom invoegt. Gebruik VLOOKUP alleen als de werkmap in Excel 2019 of ouder moet werken.
Die compatibiliteit is het belangrijkste voorbehoud. XLOOKUP bestaat niet in Excel 2016 of 2019, dus een bestand vol XLOOKUP-formules toont #NAAM?-fouten (#NAME? in Engelse Excel) voor iedereen die nog op die versies zit.
Let op de namen en het scheidingsteken. In Nederlandstalige Excel heten de functies anders: VLOOKUP is VERT.ZOEKEN, XLOOKUP is X.ZOEKEN, HLOOKUP is HORIZ.ZOEKEN, MATCH is VERGELIJKEN, IFNA is ALS.NB, FALSE is ONWAAR en TRUE is WAAR. Argumenten worden gescheiden door een puntkomma (;). De formules in dit artikel staan in de Nederlandse vorm; bij de syntaxis hieronder vind je ook de Engelse vorm voor wie Engelse Excel gebruikt (daar vervang je de puntkomma's door komma's). De namen XLOOKUP en VLOOKUP gebruiken we in de tekst, omdat je daar online op zoekt.
VLOOKUP (VERT.ZOEKEN) | XLOOKUP (X.ZOEKEN) | |
|---|---|---|
| Naar links zoeken (een kolom links van de zoekkolom teruggeven) | Nee | Ja |
| Standaard overeenkomsttype | Bij benadering (tenzij je ONWAAR toevoegt) | Exact |
| Overleeft het invoegen of verwijderen van kolommen | Nee, het kolomnummer blijft vast | Ja, bereiken passen zich aan |
| Eigen melding "niet gevonden" | Vereist ALS.NB of ALS.FOUT | Ingebouwd (if_not_found) |
| Meerdere kolommen tegelijk teruggeven | Niet gemakkelijk | Ja, resultaten lopen over |
| Zoeken vanaf de laatste overeenkomst omhoog | Nee | Ja (search_mode -1) |
| Overeenkomst bij benadering | Gegevens moeten oplopend gesorteerd zijn | Volgende kleinere of grotere, sorteren niet nodig |
Jokertekens (*, ?) | Ja, met exacte overeenkomst | Ja, met match_mode 2 |
| Horizontaal zoeken | Nee, daarvoor is HLOOKUP | Ja, dezelfde functie |
| Snelheid | Geen praktisch verschil in gangbare werkmappen | Hetzelfde, plus optioneel binair zoeken op gesorteerde gegevens |
| Beschikbaar in | Elke versie van Excel | Microsoft 365, Excel 2021, Excel 2024, Excel voor het web, mobiele apps |
De syntaxis naast elkaar
Engelse Excel:
=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
Nederlandse Excel:
=VERT.ZOEKEN(zoekwaarde; tabelmatrix; kolomindex_getal; [benaderen])
=X.ZOEKEN(zoekwaarde; zoekmatrix; retourmatrix; [indien_niet_gevonden]; [overeenkomstmodus]; [zoekmodus])
Het grote verschil is hoe je Excel vertelt waar het antwoord staat. VLOOKUP neemt een hele tabel en een kolomnummer, geteld vanaf de linkerrand van die tabel. XLOOKUP neemt twee afzonderlijke bereiken: de kolom waarin gezocht wordt en de kolom waaruit het resultaat komt.
Dezelfde zoekactie op beide manieren
Hier is een kleine productlijst in A1:D6, met een product-ID getypt in G2:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product-ID | Product | Categorie | Prijs |
| 2 | P-101 | Draadloze muis | Accessoires | $24,99 |
| 3 | P-102 | USB-C-hub | Accessoires | $39,50 |
| 4 | P-103 | Laptopstandaard | Kantoor | $45,00 |
| 5 | P-104 | Webcam HD | Video | $59,00 |
| 6 | P-105 | Bureaulamp | Kantoor | $32,75 |
Zo vind je de prijs van P-104:
=VERT.ZOEKEN(G2; A2:D6; 4; ONWAAR)
=X.ZOEKEN(G2; A2:A6; D2:D6)
Beide geven $59,00 terug. VLOOKUP doorzoekt de eerste kolom van A2:D6 en geeft de 4e kolom terug; ONWAAR (FALSE) dwingt een exacte overeenkomst af. XLOOKUP doorzoekt A2:A6 en geeft de overeenkomstige cel uit D2:D6 terug, met exacte overeenkomst als standaard.
Als je de formule door een kolom heen kopieert, vergrendel de tabel dan met absolute verwijzingen, bijvoorbeeld =X.ZOEKEN(G2; $A$2:$A$6; $D$2:$D$6). Druk in Windows op F4 of op de Mac op Cmd+T terwijl de cursor in een verwijzing staat om de $-tekens toe te voegen.
De twee klassieke valkuilen van VLOOKUP
1. ONWAAR (of 0) vergeten
Het laatste argument van VLOOKUP is optioneel, en als je het weglaat, gebruikt Excel overeenkomst bij benadering. Bij benadering gaat VLOOKUP ervan uit dat de eerste kolom gesorteerd is en geeft het de dichtstbijzijnde waarde terug die kleiner dan of gelijk aan je zoekwaarde is, zonder enige waarschuwing.
Probeer =VERT.ZOEKEN("P-110"; A2:D6; 4) op de tabel hierboven. P-110 bestaat niet, maar in plaats van #N/B (#N/A in Engelse Excel) krijg je $32,75, de prijs van P-105, omdat "P-105" de dichtstbijzijnde kleinere waarde is. Bij ongesorteerde gegevens kunnen de resultaten nog willekeuriger zijn.
Sluit een VLOOKUP met exacte overeenkomst dus altijd af met ONWAAR of 0. XLOOKUP vermijdt het probleem, omdat het een exacte overeenkomst gebruikt tenzij je anders vraagt.
2. Het vastgelegde kolomnummer
De 4 in de VLOOKUP-formule is gewoon een getal. Als iemand een kolom Leverancier invoegt tussen Product en Categorie, wordt de tabel A:E, verhuist Prijs naar kolom E en geeft de formule nog steeds de 4e kolom terug, en dat is nu Categorie. Je zou "Video" zien in plaats van $59,00.
XLOOKUP wijst naar de prijskolom zelf (D2:D6), dus Excel werkt dat automatisch bij naar E2:E6 en blijft het antwoord juist.
Wat XLOOKUP kan en VLOOKUP niet
Naar links zoeken
VLOOKUP kan alleen kolommen rechts van de zoekkolom teruggeven. XLOOKUP maakt de richting niet uit. Zo vind je het product-ID bij een productnaam in G2:
=X.ZOEKEN(G2; B2:B6; A2:A6)
Met "Bureaulamp" in G2 geeft dit P-105 terug, ook al staat kolom A links van kolom B.
Een vriendelijke melding tonen als niets wordt gevonden
=X.ZOEKEN(G2; A2:A6; D2:D6; "Niet gevonden")
Het VLOOKUP-equivalent heeft een omhulling nodig: =ALS.NB(VERT.ZOEKEN(G2; A2:D6; 4; ONWAAR); "Niet gevonden").
Meerdere kolommen teruggeven in één formule
=X.ZOEKEN(G2; A2:A6; B2:D6)
Voor P-104 lopen Webcam HD, Video en $59,00 over in drie cellen naast elkaar. Laat die cellen leeg, anders krijg je een #OVERLOOP!-fout (#SPILL! in Engelse Excel).
De laatste overeenkomst vinden in plaats van de eerste
In een logboek waarin dezelfde klant vaak voorkomt, stel je search_mode in op -1 om van onder naar boven te zoeken en de meest recente invoer te krijgen:
=X.ZOEKEN(G2; A2:A500; C2:C500; "Niet gevonden"; 0; -1)
Overeenkomst bij benadering zonder sorteren
Voor gestaffelde prijzen of commissiepercentages werkt de benadering van VLOOKUP alleen als de eerste kolom oplopend is gesorteerd. De match_mode -1 van XLOOKUP (exacte overeenkomst, of het volgende kleinere item) en 1 (exacte overeenkomst, of het volgende grotere item) hebben geen gesorteerde gegevens nodig. Met minimale bestelbedragen in J2:J5 en kortingen in K2:K5:
=VERT.ZOEKEN(B2; J2:K5; 2; WAAR)
=X.ZOEKEN(B2; J2:J5; K2:K5; ; -1)
Jokertekens
Beide ondersteunen * en ?, maar XLOOKUP heeft match_mode 2 nodig om ze als jokertekens te behandelen:
=VERT.ZOEKEN("Bureau*"; B2:D6; 3; ONWAAR)
=X.ZOEKEN("Bureau*"; B2:B6; D2:D6; ; 2)
Beide geven $32,75 terug voor Bureaulamp.
INDEX/VERGELIJKEN: het klassieke alternatief
Vóór XLOOKUP was de standaardoplossing voor de beperkingen van VLOOKUP INDEX met MATCH (in het Nederlands VERGELIJKEN). Het zoekt naar links, overleeft ingevoegde kolommen en werkt in elke versie van Excel, wat het de beste keuze maakt voor bestanden die in Excel 2019 of ouder moeten openen:
=INDEX(D2:D6; VERGELIJKEN(G2; A2:A6; 0))
=INDEX(A2:A6; VERGELIJKEN(G2; B2:B6; 0))
De eerste geeft de prijs voor het ID in G2 terug; de tweede is het voorbeeld van naar links zoeken. De 0 in VERGELIJKEN betekent exacte overeenkomst en is, net als de ONWAAR van VLOOKUP, makkelijk te vergeten. XLOOKUP is gewoon de kortere, veiligere versie van hetzelfde idee.
VLOOKUP-formules omzetten naar XLOOKUP
Er is geen ingebouwde omzetter, maar de vertaling is mechanisch:
- Zoek je VLOOKUP's: druk op
Ctrl+F(Cmd+Fop Mac), typ VERT.ZOEKEN (of VLOOKUP in Engelse Excel), klik op Opties als de extra instellingen verborgen zijn, stel Zoeken in in op Formules en klik op Alles zoeken. - Laat het eerste argument (de zoekwaarde) zoals het is.
- Vervang de tabel door de eerste kolom ervan als
lookup_array. - Vervang het kolomnummer door de overeenkomstige kolom als
return_array. Kolom 5 van een tabel die in kolom A begint, is kolom E. - Laat
ONWAARweg. Als het origineel eindigde opWAARof geen laatste argument had, voeg danmatch_mode-1 toe om het gedrag "volgende kleinere" te behouden.
Bijvoorbeeld:
=VERT.ZOEKEN(A2; Producten!$A$2:$F$500; 5; ONWAAR)
=X.ZOEKEN(A2; Producten!$A$2:$A$500; Producten!$E$2:$E$500)
Test een paar resultaten tegen de oude formule voordat je een heel blad omzet. VLOOKUP's met overeenkomst bij benadering op ongesorteerde gegevens kunnen al die tijd verkeerde waarden hebben teruggegeven, dus de antwoorden van XLOOKUP kunnen terecht afwijken.
Wat er gebeurt als een XLOOKUP-bestand opent in Excel 2016 of 2019
De werkmap opent, maar die versies kennen de functie niet. In de formulebalk verschijnt hij als =_xlfn.XLOOKUP(...). De cellen kunnen in eerste instantie nog de laatst opgeslagen resultaten tonen, maar zodra de formule opnieuw wordt berekend, geeft hij #NAAM? terug.
Als je bestanden deelt met mensen op oudere versies:
- Gebruik in plaats daarvan INDEX/VERGELIJKEN of VLOOKUP, of
- Stuur een kopie waarin de resultaten als waarden zijn geplakt (Kopiëren → Plakken speciaal → Waarden).
Hetzelfde geldt voor andere nieuwere functies zoals FILTEREN (FILTER), UNIEK (UNIQUE) en X.VERGELIJKEN (XMATCH).
XLOOKUP in Google Sheets
Google Sheets heeft ook XLOOKUP, met dezelfde volgorde van argumenten: =XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode]). De logica uit dit artikel werkt daar ook (let op de functienamen en het scheidingsteken die je taalinstelling van Sheets gebruikt), dus XLOOKUP is een veilige keuze voor bestanden die tussen Excel en Sheets heen en weer gaan.
Waarom je zoekactie #N/B of de verkeerde waarde teruggeeft
- Extra spaties. "P-104 " met een spatie aan het eind komt niet overeen met "P-104". Maak de gegevens schoon met
SPATIES.WISSEN(TRIM). - Getallen die als tekst zijn opgeslagen. Een ID dat als tekst is getypt, komt niet overeen met hetzelfde ID als getal. Zoek naar het groene driehoekje in de hoek van de cel en zet de kolom om naar één type.
- Ontbrekende ONWAAR in VLOOKUP. Je krijgt een aannemelijk maar fout antwoord in plaats van
#N/B. - Bereiken die zijn verschoven toen je de formule kopieerde. Gebruik absolute
$-verwijzingen voor de zoektabel. #VERW!van VLOOKUP. Het kolomnummer is groter dan het aantal kolommen in de tabel.#WAARDE!van XLOOKUP. Het zoekbereik en het retourbereik hebben verschillende groottes, bijvoorbeeld A2:A6 en D2:D60.#NAAM?De functienaam is verkeerd gespeld, of het bestand is geopend in een versie zonder XLOOKUP.
Veelgestelde vragen
Is XLOOKUP beter dan VLOOKUP? Ja, voor bijna elk gebruik. Het heeft standaard een exacte overeenkomst, zoekt in elke richting, gaat niet kapot als kolommen verschuiven en gaat zonder omhulling om met ontbrekende waarden. Het enige echte voordeel van VLOOKUP is dat het in elke versie van Excel werkt.
Is XLOOKUP sneller dan VLOOKUP? In dagelijkse werkmappen merk je geen verschil. Bij zeer grote gesorteerde lijsten kunnen de binaire zoekmodi van XLOOKUP (search_mode 2 of -2) dingen versnellen, maar ze geven verkeerde resultaten als de gegevens niet gesorteerd zijn.
Waarom toont XLOOKUP #NAAM? op mijn computer? Je Excel-versie bevat XLOOKUP niet. Het zit in Microsoft 365, Excel 2021 en later en Excel voor het web, maar niet in Excel 2019 of 2016. Gebruik INDEX/VERGELIJKEN.
Vervangt XLOOKUP ook HLOOKUP? Ja. Als het zoekbereik en het retourbereik rijen zijn in plaats van kolommen, zoekt XLOOKUP horizontaal met dezelfde syntaxis.
Kan XLOOKUP alle overeenkomsten teruggeven, niet alleen de eerste? Nee, het geeft één overeenkomst (of één rij kolommen) terug. Gebruik FILTEREN om elke overeenkomende rij te krijgen, bijvoorbeeld =FILTEREN(A2:D6; C2:C6="Kantoor").
Moet ik VLOOKUP nog leren? Het is de moeite waard om het te herkennen, want je vindt het in oudere werkmappen en sjablonen. Leer voor nieuwe formules XLOOKUP en houd INDEX/VERGELIJKEN in gedachten voor bestanden die in oudere Excel moeten openen.
Laat AI de zoekformule voor je schrijven
Bij zoekformules veroorzaken kleine details, zoals een ontbrekende ONWAAR of een verschoven bereik, de meeste stille fouten. Met CoreGPT voor Excel beschrijf je wat je nodig hebt, zoals "zoek de prijs op voor het product-ID in G2" of "markeer elke rij als boven of onder het doel", en het schrijft de formule met een korte uitleg in je blad. Gratis om mee te beginnen en werkt in Excel voor Windows, Mac en het web.
CoreGPTGebruik deze formule en vul hem door naar beneden:
=IF(C2>=D2,"Boven","Onder")Probeer het: CoreGPT voor Excel
Lees ook:
