XLOOKUP() vs. VLOOKUP(): een vergelijking voor Excel-gebruikers

XLOOKUP() en VLOOKUP() syntaxis

Nu je de basis van beide functies kent, bekijken we hun syntaxis en hoe je ze in de praktijk gebruikt. 

Syntaxis van VLOOKUP()

Laten we de syntaxis van VLOOKUP() bekijken. 

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

In de bovenstaande formule:

• lookup_value is de waarde waarnaar je wilt zoeken. Dit kan een getal, tekst of een verwijzing naar een cel met de zoekwaarde zijn.

• table_array is het celbereik dat de data bevat. De eerste kolom in dit bereik moet de search_key bevatten.

• col_index_num is het kolomnummer in het bereik waarvan je de waarde wilt ophalen. 

• range_lookup is waar je een logische waarde (TRUE of FALSE) invoert.

Syntaxis van XLOOKUP()

Laten we nu de syntaxis van xlookup() bekijken. Zoals je ziet heeft XLOOKUP() meer argumenten dan VLOOKUP().

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

In de bovenstaande formule:

• lookup_value is de waarde waarnaar je wilt zoeken.

• lookup_array is het celbereik waarin naar de zoekwaarde wordt gezocht.

• return_array is het celbereik dat de waarde bevat die je wilt retourneren.

• if_not_found (optioneel) retourneert een waarde als er geen overeenkomst is gevonden. Als de waarde niet is opgegeven, retourneert de functie #N/A.

• match_mode (optioneel) bepaalt het type overeenkomst. 0 is voor een exacte overeenkomst (standaard). -1 is voor een exacte overeenkomst of het eerstvolgende kleinere item. 1 is voor een exacte overeenkomst of het eerstvolgende grotere item. 2 is voor overeenkomsten met jokertekens.

• search_mode (optioneel) bepaalt de zoekmodus. 1 zoekt van eerst naar laatst (standaard). -1 zoekt van laatst naar eerst. 2 voert een binaire zoekopdracht in oplopende volgorde uit. -2 voert een binaire zoekopdracht in aflopende volgorde uit.

Belangrijkste verschillen: XLOOKUP() vs. VLOOKUP()

Laten we nu enkele belangrijke verschillen tussen XLOOKUP() en VLOOKUP() verkennen.

Exacte-overeenkomstmodus 

XLOOKUP() gebruikt standaard de modus voor exacte overeenkomst, maar bij VLOOKUP() moet je FALSE opgeven voor een exacte overeenkomst.

Verschil in exacte overeenkomst tussen XLOOKUP() en VLOOKUP(). Bron: afbeelding door auteur.

In VLOOKUP() heb ik de waarde range_lookup (4e argument) niet opgegeven, dus gaf de functie de dichtstbijzijnde overeenkomst, wat niet het gewenste resultaat is. XLOOKUP() daarentegen retourneert standaard de exacte overeenkomst.

Zoekrichting

VLOOKUP() is beperkt tot zoeken alleen rechts van de eerste kolom in de geselecteerde tabelarray. XLOOKUP() kan echter in elke richting naar waarden zoeken.

Verschil in zoekrichting tussen VLOOKUP() en XLOOKUP(). Bron: afbeelding door auteur.

Hier vond VLOOKUP() geen cijfers, omdat de functie alleen rechts van de lookup_value (studentnaam) kan zoeken. XLOOKUP() vindt daarentegen de cijfers voor Robin, omdat de functie in beide richtingen (links of rechts) kan zoeken.

Arrayverwijzingen

In VLOOKUP() moet je het volledige databereik (tabelarray) definiëren en het kolomnummer (kolomindex) opgeven dat de gewenste output (retourkolom) bevat, allemaal binnen één formule. XLOOKUP() is flexibeler. Je kunt afzonderlijke arrays definiëren voor de zoekwaarde en de data die je wilt retourneren. 

Arrayverwijzingen tussen VLOOKUP() en XLOOKUP(). Bron: afbeelding door auteur.

Horizontaal zoeken

VLOOKUP() laat geen horizontale zoekopdrachten toe. Als je horizontaal wilt zoeken, moet je in plaats daarvan HLOOKUP() gebruiken. XLOOKUP() combineert deze twee functies, omdat het zowel verticale als horizontale zoekopdrachten uitvoert, zodat je geen aparte functies voor verschillende richtingen hoeft te gebruiken.

Verschil in horizontaal zoeken tussen VLOOKUP() en XLOOKUP(). Bron: afbeelding door auteur.

Omgaan met kolom-invoegingen/-verwijderingen 

Kolomwijzigingen beïnvloeden VLOOKUP() vanwege de hardgecodeerde column_index_num. XLOOKUP() wordt echter niet beïnvloed door kolomwijzigingen en blijft functioneren zonder de formule aan te passen.

XLOOKUP() blijft onaangetast, terwijl VLOOKUP() faalt. Bron: afbeelding door auteur.

Sorteren en zoeken

Je kunt VLOOKUP() oplopend sorteren, maar dit leidt tot problemen bij ongesorteerde data. XLOOKUP() kan daarentegen het argument search_mode gebruiken om zowel in oplopende als aflopende volgorde te zoeken.

Omgaan met invoegen/verwijderen in XLOOKUP() en VLOOKUP(). Bron: afbeelding door auteur.

Aangepaste foutmeldingen

VLOOKUP() toont een #N/A-fout als er geen overeenkomst wordt gevonden. XLOOKUP() heeft een optionele parameter if_not_found waarmee je de uitvoer kunt aanpassen als een waarde niet wordt gevonden. 

Aangepaste foutmeldingen in XLOOKUP() en VLOOKUP(). Bron: afbeelding door auteur.

Je ziet dat VLOOKUP() de student niet in de lijst kon vinden en een #N/A-fout weergaf. Op dezelfde manier kon XLOOKUP() de naam van de student niet vinden. Maar in tegenstelling tot VLOOKUP() geeft XLOOKUP() een specifieke melding terug als er geen overeenkomst is.

Meerdere waarden retourneren

VLOOKUP() kan maar één waarde tegelijk retourneren. XLOOKUP() kan daarentegen waarden uit meerdere kolommen tegelijk ophalen. Daarom kun je één XLOOKUP()-formule gebruiken in plaats van meerdere VLOOKUP()-formules.

Meerdere waarden retourneren met XLOOKUP() en VLOOKUP(). Bron: afbeelding door auteur.

Zoekmodus

VLOOKUP() doorzoekt een lijst vanaf het begin en retourneert alleen de eerste waarde die overeenkomt. XLOOKUP() kan een lijst in beide richtingen scannen (van boven naar beneden of van onder naar boven) en snel resultaten vinden in uitgebreide lijsten.

Verschil tussen VLOOKUP() en XLOOKUP(). Bron: afbeelding door auteur.

Hier haalt VLOOKUP() het eerste voorkomen op, wat uit het eerste semester is. Met XLOOKUP() gebruik ik echter de zoekmoduscode -1 om van onder naar boven te zoeken.

Praktische voorbeelden en usecases

Laten we nu enkele praktische voorbeelden en usecases bekijken.

Zoeken gebruiken zonder een tabel te hoeven herschikken

Ik heb een lijst met studentnamen en scores, en ik wil de score van elke student vinden op basis van zijn of haar naam. Hiervoor gebruik ik zowel XLOOKUP() als VLOOKUP() om je de verschillen te laten zien. 

Bij gebruik van XLOOKUP() voer ik de volgende formule in:

=XLOOKUP(D8,B2:B5,A2:A5)

In deze formule:

• D8 bevat de zoekwaarde, namelijk Charlie.

• B2:B5 bevat de studentnamen. De functie XLOOKUP() zoekt binnen dit bereik naar de waarde in D8.

• A2:A5 is het bereik met de scores die bij de studentnamen horen. 

Bij gebruik van VLOOKUP() voer ik de volgende formule in:

=VLOOKUP(D4,A1:B5,1,0)

In deze formule:

• D4 bevat de zoekwaarde, namelijk Charlie.

• A1:B5 doorzoekt alle kolommen (Scores, Student Name).

• 1 retourneert de waarde uit de 1e kolom van het bereik (Scores).

• 0 haalt de exacte overeenkomst op.

VLOOKUP() geeft een #N/A-fout, maar XLOOKUP() kan het aan. Bron: afbeelding door auteur.

Zoals je ziet toont VLOOKUP() een #N/A-fout, omdat de functie niet naar links kan zoeken.

Een aangepaste output maken als een waarde niet wordt gevonden

Ik heb een lijst met studenten met hun ID's, namen en cijfers. Tijdens het zoeken heb ik per ongeluk een studenten-ID ingevoerd die niet op de lijst stond. Om dit op te vangen wil ik dat er een aangepaste melding verschijnt wanneer de studenten-ID niet gevonden wordt. Ik gebruik zowel XLOOKUP() als VLOOKUP() om je de verschillen te laten zien. 

Bij gebruik van XLOOKUP() voer ik de volgende formule in:

(=XLOOKUP(E9, A2:A5, B2:B5, "Student not Found")) 

In deze formule:

• E9 bevat de waarde waarnaar we willen zoeken. In mijn geval is dat 14256.

• A2:A5 is het celbereik met de zoekwaarde (Student ID).

• B2:B5 zoekt naar de score van ID 14526.

• Student not Found wordt weergegeven als de score niet op de lijst staat. 

Bij gebruik van VLOOKUP() voer ik de volgende formule in:

(=VLOOKUP(E6, A2:C5,2,0)) 

In deze formule:

• E6 verwijst naar Student ID, waarnaar ik zoek.

• A2:C5 verwijst naar het bereik dat alle data in de tabel bevat.

• 2 geeft de kolom aan waaruit de data wordt opgehaald.

• 0 haalt de exacte overeenkomst op.

XLOOKUP() geeft een melding terug, VLOOKUP() niet. Bron: afbeelding door auteur.

Je ziet dat 14256 niet voorkomt in het bereik A2:A5, dus retourneert XLOOKUP() de aangepaste melding Student not found. En VLOOKUP() geeft daarentegen een #N/A-fout. 

Zoeken van onder naar boven

Ik wil Sarah's salaris vinden van het meest recente jaar. Hiervoor gebruik ik zowel XLOOKUP() als  VLOOKUP() om je de verschillen te laten zien. 

Bij gebruik van XLOOKUP() voer ik de volgende formule in:

=XLOOKUP(F8,B2:B10,C2:C10,,,-1)

In deze formule:

• F8 bevat de zoekwaarde Sarah.

• B2:B10 kijkt in het bereik en zoekt naar Sarah. 

• C2:C10 haalt de data op.

• -1 zoekt vanaf de onderkant van de kolom. 

Bij gebruik van VLOOKUP() voer ik de volgende formule in:

=VLOOKUP(F4, B2:C10,2,0)

In deze formule:

• F4 bevat de zoekwaarde Sarah.

• B2:C10 zoekt naar de zoekwaarde binnen dit bereik.

• 2 geeft het kolomnummer aan Salary waaruit de data wordt opgehaald.

• 0 haalt de exacte overeenkomst op.

Verschil tussen VLOOKUP() en XLOOKUP() in zoekmodus. Bron: afbeelding door auteur.

Je ziet dat XLOOKUP() het gewenste salaris voor het gewenste jaar vindt, terwijl VLOOKUP() alleen de eerste overeenkomst toont. Dit lukt omdat XLOOKUP() het mogelijk maakt om van onder naar boven te zoeken.

Prestaties en compatibiliteit

Laten we nu de prestaties en compatibiliteit van beide functies vergelijken. 

Prestaties bij grote datasets

VLOOKUP() kan trager zijn bij grote datasets, vooral als de zoekkolom niet is gesorteerd en het argument range lookup is ingesteld op FALSE voor een exacte overeenkomst. Dit komt doordat VLOOKUP() de dataset sequentieel scant tot er een overeenkomst is gevonden, wat tijdrovend kan zijn bij grote tabellen. XLOOKUP() biedt betere prestaties bij grote datasets. De functie kan exacte overeenkomsten efficiënter afhandelen en zowel horizontaal als verticaal zoeken zonder te sorteren. 

Compatibiliteit met verschillende Excel-versies

Misschien denk je nu aan het afzweren van VLOOKUP() na het zien van de voordelen van XLOOKUP(). Maar om het te verduidelijken—hoewel XLOOKUP() veel sneller is en een uitstekende feature, zijn er momenten waarop het niet beschikbaar is. Dit komt omdat XLOOKUP() een nieuwere feature is die alleen werkt in Excel 2021 en Microsoft 365 (vanaf 2019). Als je samenwerkt met mensen die oudere versies van Excel gebruiken, moet je VLOOKUP() en andere functies gebruiken in plaats van XLOOKUP().

Samenvattende tabel

Laten we een samenvattende tabel maken voor snelle referentie. 

Conclusie

XLOOKUP() presteert beter dan VLOOKUP(), vooral bij grote datasets of complexe zoekvereisten. De flexibiliteit—zoals bidirectioneel zoeken, meerdere resultaten ophalen en aanpasbaarheid bij dataveranderingen—maakt het een handig hulpmiddel. Toch heeft VLOOKUP() nog steeds zijn plek, zeker voor gebruikers van oudere Excel-versies of wie een eenvoudigere aanpak bij basiszoekopdrachten verkiest. De beste keuze hangt uiteindelijk af van jouw specifieke behoeften en Excel-versie.

Als je nieuw bent met Excel of een sterke basis wilt opbouwen, zijn onze cursus Introduction to Excel en de Excel Fundamentals-skilltrack goede startpunten om de basics te beheersen.