INDEX en VERGELIJKEN in Excel: uitleg en voorbeelden

INDEX doet iets heel eenvoudigs: je wijst een bereik aan en zegt welke positie je wilt. =INDEX(C2:C50;4) geeft de vierde waarde uit C2:C50, dus de waarde in C5.

Heeft het bereik meerdere kolommen, dan geef je ook een kolomnummer op. =INDEX(B2:E20;3;2) geeft de waarde in de derde rij en de tweede kolom van dat bereik, dus C4.

Net als bij VERT.ZOEKEN tel je binnen het bereik. De eerste rij van het bereik is 1, ook als het bereik op rij 2 of rij 10 begint. Vraag je een positie buiten het bereik, dan krijg je #VERW!.

In je eentje is INDEX niet zo handig, want meestal weet je de positie niet. Je weet wel wát je zoekt, zoals een productcode of een naam. Daarvoor heb je VERGELIJKEN nodig: die functie zet een zoekwaarde om in een positie.

VERGELIJKEN zoekt een waarde op in een lijst en geeft de positie terug. =VERGELIJKEN("P-104";A2:A50;0) geeft bijvoorbeeld 4 als P-104 op de vierde plek staat. Die positie geef je door aan INDEX:

• =INDEX(C2:C50;VERGELIJKEN(E2;A2:A50;0))

Lees de formule van binnen naar buiten. VERGELIJKEN zoekt de code uit E2 in kolom A en vindt de positie. INDEX haalt op diezelfde positie de waarde uit kolom C.

Zorg dat beide bereiken op dezelfde rij beginnen en even lang zijn, zoals A2:A50 en C2:C50. Anders klopt de positie niet en krijg je de waarde van een andere rij.

Vergeet de 0 als laatste argument van VERGELIJKEN niet. Die zorgt voor een exacte overeenkomst. Zonder 0 zoekt VERGELIJKEN bij benadering en krijg je in een ongesorteerde lijst een verkeerd antwoord.

Voordat X.ZOEKEN bestond, was INDEX met VERGELIJKEN de favoriete combinatie van ervaren Excel-gebruikers. Het heeft drie voordelen boven VERT.ZOEKEN:

• Naar links zoeken. Het zoekbereik en het antwoordbereik staan los van elkaar, dus de antwoordkolom mag links van de zoekkolom staan.
• Geen vast kolomnummer. Voeg je een kolom in, dan schuiven beide bereiken mee en blijft de formule kloppen.
• Alleen de nodige kolommen. Je verwijst naar twee kolommen in plaats van een hele tabel. Bij grote bestanden rekent dat sneller.

En anders dan X.ZOEKEN werkt deze combinatie in elke versie van Excel, ook in Excel 2010 en 2016. Deel je een bestand met veel verschillende mensen, dan is dat een groot pluspunt.

Met twee keer VERGELIJKEN zoek je in twee richtingen. Denk aan een tabel met producten in kolom A en maanden in rij 1. Je wilt de omzet van één product in één maand:

• =INDEX(B2:M20;VERGELIJKEN(P1;A2:A20;0);VERGELIJKEN(P2;B1:M1;0))

De eerste VERGELIJKEN zoekt de rij van het product, de tweede de kolom van de maand. INDEX geeft de waarde op het kruispunt.

Let erop dat het bereik van INDEX precies aansluit op beide zoekbereiken. B2:M20 begint in dezelfde rij als A2:A20 en in dezelfde kolom als B1:M1. Schuift er één bereik een rij of kolom op, dan krijg je de waarde die er net naast ligt. Zulke opzoektabellen kom je vaak tegen bij prijzen per maat en kleur of bij tarieven per zone en gewicht.

Wil je zoeken op twee voorwaarden, bijvoorbeeld een productcode en een maat, dan laat je VERGELIJKEN zoeken naar een 1 in een reeks voorwaarden:

• =INDEX(C2:C50;VERGELIJKEN(1;(A2:A50=F1)*(B2:B50=F2);0))

Het deel (A2:A50=F1)*(B2:B50=F2) geeft voor elke rij een 1 als beide voorwaarden kloppen en anders een 0. VERGELIJKEN zoekt de eerste 1 en INDEX haalt de waarde op.

In Microsoft 365 werkt dit gewoon met Enter. In Excel 2019 en ouder is het een matrixformule: daar bevestig je met Ctrl + Shift + Enter. Excel zet dan accolades om de formule.

Een eenvoudiger alternatief is een hulpkolom waarin je de twee waarden samenvoegt, zoals =A2&"-"&B2. Daarin zoek je dan met een gewone VERGELIJKEN.