Gebruik INDEX en MATCH i.p.v. VLOOKUP
Er zijn meerdere manieren in Excel om te zoeken. Bekend zijn VLOOKUP en de combinatie van de INDEX en MATCH. Tot onze grote frustratie wordt de VLOOKUP veel vaker gebruikt dan de combinatie INDEX en MATCH, terwijl deze laatste optie echt veel beter is. Minder kans op fouten en een stuk efficiënter.
Hoe werken deze functies?
Eerst leggen we je uit hoe de functies VLOOKUP, MATCH en INDEX (Nederlands: VERT.ZOEKEN, VERGELIJKEN en INDEX) werken. Daarna vertellen we je waarom we liever een combinatie van de laatste twee gebruiken dan de eerste.
VLOOKUP
Met de VLOOKUP (Nederlands: VERT.ZOEKEN) zoek je naar een waarde in een bepaalde kolom van een tabel, op basis van een gekozen waarde in de eerste kolom van dezelfde tabel. Het format is: =VLOOKUP(D1;A1:C10;3;FALSE). D1 is een referentie naar de waarde die je zoekt in de eerste kolom, A1:C10 is de tabel waar het over gaat, de 3 betekent dat je een resultaat zoekt in de 3e kolom van de tabel en FALSE betekent dat je een exacte zoekopdracht uitvoert (niet bij benadering).
N.B. VLOOKUP in het Nederlands is VERT.ZOEKEN, dus als je de VLOOKUP functie in een Nederlandse versie van Excel gebruikt, schrijf dan ‘VERT.ZOEKEN’.
MATCH
Met de MATCH (Nederlands: VERGELIJKEN) functie bepaal je waar in een bereik een bepaalde waarde voorkomt. Het format is: =MATCH(D1;A1:A10;0). D1 is weer de waarde die je zoekt. A1:A10 is het bereik waarbinnen je zoekt en de 0 betekent dat je exact wilt zoeken.
N.B. MATCH in het Nederlands is VERGELIJKEN, dus als je de MATCH functie in een Nederlandse versie van Excel gebruikt, schrijf dan ‘VERGELIJKEN’.
INDEX
Met de INDEX functie haal je de zoveelste waarde uit een kolom op. De “zoveelste” waarde is vaak door een MATCH bepaald. Het format is: =INDEX(C1:C10;X) waarin C1:C10 de rij is waaruit je de X’ste waarde nodig hebt.
Vaak worden INDEX en MATCH gecombineerd en dan krijg je een formule zoals: =INDEX(C1:C10;MATCH(D1;A1:A10;0)).
N.B. INDEX in het Nederlands is INDEX, dus de INDEX functie schrijf je ook in de Nederlandstalige versie van Excel gewoon als ‘INDEX’.
Dus… INDEX en MATCH!
Ajajaj, twee alinea’s om de INDEX en MATCH uit te leggen en slechts één voor de VLOOKUP. Vind je het gek dat de VLOOKUP populairder is? Als je het zo bekijkt niet, maar vergelijk het eens met kamperen: dat is ook echt fijner met een binnen- en een buitentent, al duurt het iets langer om op te zetten. Neem die moeite! Dit is waarom:
1) De numerieke referentie (3, in het voorbeeld) die aangeeft uit welke kolom de VLOOKUP functie een resultaat moet halen is een statisch gegeven. Als iemand ooit een keer een kolom tussenvoegt in de tabel – en dit gebeurt heel vaak – dan werkt de functie niet meer, want hij blijft iets uit de derde kolom halen terwijl het de vierde zou moeten zijn. Zelf zou je er misschien nog aan denken om het getal aan te passen, maar denk aan de toekomstige gebruiker! We kunnen dat niet vaak genoeg herhalen!
2) De noodzakelijke waarde “FALSE” wordt heel vaak vergeten in de VLOOKUP functie. Wordt deze weggelaten of vervangen door TRUE dan zoekt de functie “ongeveer” en dat geeft zelden het resultaat dat je nodig hebt. In de MATCH functie moet weliswaar ook een waarde voor exact zoeken worden ingegeven (0), maar om een of andere reden wordt die minder vaak vergeten.
3) Bij omvangrijke tabellen en lange ketens van verwijzingen kunnen de INDEX en MATCH functie veel sneller zijn dan de VLOOKUP. Zeker als je meerdere keren iets moet vinden op basis van dezelfde match. Dan maak je een aparte kolom aan voor de MATCH en verwijs je steeds naar die kolom in meerdere INDEX formules. Veel gebruikers vergeten dit, zelfs als ze bekend zijn met de INDEX en MATCH functie, maar het maakt de spreadsheet razendsnel!
Lees ook over de toepassing van INDEX & MATCH in het artikel:
Wat kan PerfectXL doen?
Wat kan PerfectXL in dit geval voor je betekenen? PerfectXL Risk Finder heeft een risicomelding die heet Approximate Lookup. Die zoekt naar VLOOKUP of HLOOKUP functies met TRUE als laatste variabele of zonder laatste variabele, want dit is een zeer veelvoorkomende fout in Excel. PerfectXL wijst je ook op vaste getallen in zoekfuncties. Het is beter om deze zoveel mogelijk te voorkomen.
Screenshot
Kortom, het is even wennen, maar gebruik INDEX en MATCH wanneer je kunt!
N.B. Voor meer informatie hierover lees de blog van Felienne Hermans om te leren waarom VLOOKUP gevaarlijk is.