Iedereen die vaak met Excel werkt zal bekend zijn met de VLOOKUP functie. Eerder dit jaar heeft Microsoft een verbeterde versie beschikbaar gemaakt: XLOOKUP. Waar VLOOKUP en HLOOKUP alleen in één richting kunnen zoeken, kan XLOOKUP in beide richtingen zoeken en ondersteunt daarnaast nog een aantal nieuwe functionaliteiten.

Excel heeft lang de VLOOKUP en HLOOKUP functies in zich gehad. Met deze functies konden zogenaamde lookup tables gemaakt worden: Tabellen waar in een bepaalde kolom gezocht worden, en de waarde uit een andere kolom (in dezelfde rij) terug gegeven werd. Met dit soort tabellen kunnen verschillende sets met gegevens samengevoegd worden om verschillende berekeningen mee te maken of overzichten te tonen.

In de Nederlandstalige versie van Excel werden deze functies VERT.ZOEKEN en HORIZ.ZOEKEN genoemd. In de voorbeelden hieronder zullen wij de Engelstalige namen aanhouden. Dit zullen wij ook doen voor XLOOKUP, dat X.ZOEKEN in de Nederlandstalige versie van Excel heet.

Wil je de voorbeelden ook meteen in Excel uitproberen?
Download hier het Excelbestand.

Hoe werkt VLOOKUP ook alweer?

De VLOOKUP functie is eenvoudig. Je geeft een waarde op die opgezocht moet worden, een bereik waarin gezocht moet worden en uit welke kolom in dat bereik de resultaatwaarde gehaald moet worden.

De functie eist wel dat de data goed geformatteerd en gesorteerd is. Wanneer er bij voorbeeld meerdere matches zijn, of als de volgorde anders is dan verwacht dan kunnen onverwachte resultaten gegeven worden.

Hoe werkt XLOOKUP?

Een groot voordeel van XLOOKUP is dat je minder snel aanpassingen hoeft te doen in bestaande data om er in te zoeken. Wanneer VLOOKUP wordt gebruikt, dan moet de data vaak aangepast worden zodat er goed in gezocht en gevonden kan worden. Zo moest de tabel in de juiste richting staan (verticaal of horizontaal), gesorteerd, en met zoek– en resultaatwaardes op dezelfde regel. Wanneer er gebruik wordt gemaakt van XLOOKUP kan data zowel horizontaal als verticaal gezocht worden, en kan de lijst waarin gezocht worden los staan van de lijst waar de resultaatwaardes in staan.

In VLOOKUP wordt er alleen gezocht naar een gegeven waarde in een bepaald bereik, waaruit een kolom als resultaat teruggegeven wordt. Met XLOOKUP is het mogelijk om de zoek– en resultaatbereiken apart te definiëren. Hiernaast heeft de functie in zichzelf al de mogelijkheid om een waarde (of functie) te geven wanneer de gezochte waarde niet wordt gevonden, en geeft ook meer uitgebreide mogelijkheden m.b.t. het zoeken.

De syntaxis van XLOOKUP

De syntaxis van de functie is iets anders dan die van VLOOKUP, namelijk =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]).

Gebruik van lookup_array en return_array

Anders dan bij VLOOKUP kan XLOOKUP een willekeurig bereik van waarden nemen om in te zoeken, en een bijpassend bereik waaruit de resultaten gehaald moeten worden. Het is niet nodig om een volledige rij of kolom aan te geven, en het is ook niet vereist dat de twee bereiken naast elkaar zijn uitgelijnd. Het is dus mogelijk om bij voorbeeld in A1:A5 te zoeken, en resultaten te halen uit H9:H13 of A21:A25.

Ze moeten wel beide in dezelfde richting gaan, maar hoeven niet per se naast elkaar te staan.

Gebruik van if_not_found

Natuurlijk kan het zijn dat de gezochte waarde niet gevonden kan worden. Bij VLOOKUP was het altijd nodig om een aparte functie toe te voegen om dit af te vangen, XLOOKUP doet dit gelukkig zelf. De if_not_found parameter kan ingesteld worden als een vaste waarde, bij voorbeeld “Niet gevonden”, maar het is ook mogelijk om hier een functie te gebruiken.

Match mode en Search mode

De twee nieuwe opties match_mode en search_mode geven de gebruiker meer controle over hoe er gezocht wordt. Doordat de tabel niet meer van tevoren gesorteerd hoeft te zijn (zoals bij VLOOKUP het geval was), kan het nodig zijn om aan te geven hoe XLOOKUP de zoekfunctie moet uitvoeren.

De opties voor match_mode
0 Exacte match. Als er geen match gevonden is dan #N/A.
-1 Exact match. Als de waarde niet gevonden is, gebruik dan het volgende item dat kleiner is, die wel gevonden kan worden.
1 Exact match. Als de waarde niet gevonden is, gebruik dan het volgende item dat groter is, die wel gevonden kan worden.
2 A wildcard match where *, ?, and ~ have special meaning.

Als er geen waarde voor deze parameter wordt opgegeven, dan wordt de eerste optie (0) genomen.

De opties voor search_mode
1 Begint de zoekopdracht bij het eerste item in de zoektabel
-1 Begint de zoekopdracht bij het laatste item in de zoektabel
2

Voert een binaire zoekopdracht uit waarbij er van uit wordt gegaan dat de zoektabel in oplopende volgorde is gesorteerd. Als dat niet zo is, dan wordt een ongeldig resultaat terug gegeven.
-2

Voert een binaire zoekopdracht uit waarbij er van uit wordt gegaan dat de zoektabel in aflopende volgorde is gesorteerd. Als dat niet zo is, dan wordt een ongeldig resultaat terug gegeven.

Als er geen waarde voor deze parameter wordt opgegeven, dan wordt de eerste optie (1) genomen.

Voorbeelden

Hieronder worden via enkele voorbeelden de mogelijkheden van XLOOKUP geïllustreerd. Download hier het bijhorende Excelbestand. Elk werkblad in dit bestand bevat een voorbeeld dat hieronder worden beschreven.

VLOOKUP

Allereerst een kort voorbeeld met VLOOKUP. Wij hebben een tabel met studenten, wij willen de score van een van de studenten vinden. In cel E5 plaatsen wij de formule =VLOOKUP(D5,vlookup1,2) om in de volledige tabel te zoeken naar de inhoud van D5, en vervolgens de waarde uit de 2e kolom te tonen.

Zoeken in meerdere tabellen met if_not_found

De parameter if_not_found kan gebruikt worden om in meerdere tabellen in een keer te zoeken. Stel, wij hebben twee tabellen, maar weten niet in welke van de twee onze gezochte persoon staat, dan kunnen wij met de functie =XLOOKUP(G5, gesplitst1[Naam], gesplitst1[Score], XLOOKUP(G5, gesplitst2[Naam], gesplitst2[Score])) eerst in de eerste tabel zoeken, en als daar geen waarde gevonden wordt een tweede zoekopdracht uitvoeren in de tweede tabel.

Zoeken op delen van woorden met match_mode

Door gebruik te maken van de match_mode parameter kunnen wij op delen van namen zoeken. In dit voorbeeld vinden wij Apple door alleen “App” in te vullen. Dit werkt omdat wij voor match_mode het getal 2 in hebben gevuld, en een sterretje (*) voor en na de zoekterm. Met het sterretje kan het “onbekende” deel onbeperkt lang zijn, als er meer controle moet zijn over een bepaald patroon waarin gezocht moet worden, dan kan er ook gebruikt worden gemaakt van vraagtekens (?) en tildes (~).

Drempelwaardes met match_mode

Door gebruik te maken van match_mode kan er bij voorbeeld een berekening gemaakt worden voor bonussen, waarbij gewerkt wordt met drempelwaardes. In het voorbeeld krijgen onze sales medewerkers een bonus gebaseerd op verkoopresultaten. Afhankelijk van de behaalde resultaten wordt een bepaald percentage uitgekeerd. Door -1 als waarde door te geven als match_mode kunnen wij zoeken tot de hoogste drempelwaarde die nog wel onder het behaalde verkoopresultaat ligt.

Meerdere waardes in een keer vinden met return_array

We kunnen ook meerdere waardes in een keer terug krijgen. Dit zijn zogenaamde spilled values. In ons voorbeeld willen wij van een leerling de cijfers van alle vakken zien. Wij kunnen uiteraard drie losse XLOOKUP formules maken, maar het kan ook in een keer. Dit doen wij door in de return_array parameter meerdere velden aan te geven.

Zoeken en vergelijken

Door functies te combineren kunnen wij nog meer uit de XLOOKUP functie halen. Stel, wij hebben een tabel met cijfers van leerlingen. Uit deze tabel willen wij per vak de leerling met het hoogste cijfer weten, en daarnaast een samenvatting per vak van hoeveel leerlingen een voldoende dan wel een onvoldoende cijfer hebben behaald. Door meerdere XLOOKUP functies te combineren kunnen wij een overzicht met resultaten weergeven.

Zelf aan de slag

Bovenstaande zijn slechts enkele voorbeelden van wat er met de XLOOKUP functie gedaan kan worden. Het gebruik echt onder de knie krijgen zal alleen lukken door er zelf mee aan de slag te gaan.