Het Excel bestand dat hoort bij deze pagina over INDEX en VERGELIJKEN, staat onderaan de tekst. Je mag het gratis downloaden voor eigen gebruik.
Standaard zoekfuncties
Om informatie op te zoeken in Excel lijsten worden vaak de standaard zoekfuncties VERT.ZOEKEN en/of HORIZ.ZOEKEN gebruikt. Deze functies zijn makkelijk te onthouden, maar hebben drie belangrijke nadelen. Allereerst worden je berekeningen en bestanden trager als je deze functies veel gebruikt. Ten tweede moet dat wat je zoekt (‘zoekwaarde’) altijd in de eerste kolom staan. Tenslotte zijn deze functies niet geschikt om te zoeken in complexe tabellen. Laat ik eens beginnen met een sterk vereenvoudigd voorbeeld.
Voorbeeld 1 – Tab ‘VERT.ZOEKEN 1’
In de tabel staan verschillende Apple iPad’s met bijbehorende prijzen voor het 128 GB en 256 GB Wi-Fi model. In cel F3 kan een product worden ingevuld (‘zoekwaarde’). In de gele cellen F4 en F5 worden de gevonden prijzen getoond.
Formule F4
=VERT.ZOEKEN(F3;A1:C7;2;ONWAAR)
Formule F5
=VERT.ZOEKEN(F3;A1:C7;3;ONWAAR)
Als ik de prijzen van beide modellen wil zoeken en tonen, heb ik 2 formules nodig. Maar wat als de tabel groter wordt en ook de prijzen van de 32 GB, 64 GB, 512 GB en 1 TB modellen bevat? Als ik bovenstaand voorbeeld aanhoud, betekent dit dat ik voor alle extra modellen ook extra formules moet programmeren.
Voorbeeld 2 – Tab ‘VERT.ZOEKEN 2’
In de tabel staan verschillende Apple iPad’s met bijbehorende prijzen voor alle Wi-Fi modellen. In cel J3 kan een product worden ingevuld (‘zoekwaarde’). In de gele cellen worden de gevonden prijzen getoond.
Formule J4
=VERT.ZOEKEN(F3;A1:G7;2;ONWAAR)
…
Formule J9
=VERT.ZOEKEN(F3;A1:G7;7;ONWAAR)
Als je in grote lijsten en complexe tabellen op zoek gaat naar informatie, is het handig om de functies INDEX en VERGELIJKEN te leren gebruiken. Deze functies vervangen HORIZ.ZOEKEN en VERT.ZOEKEN en hebben 3 belangrijke voordelen. Allereerst blijven je berekeningen en bestanden sneller werken als je deze functies gebruikt. Ten tweede hoeft de zoekwaarde niet in de eerste kolom te staan. Tenslotte zijn deze functies uitermate geschikt om te zoeken in complexe tabellen.
Wat doet de functie INDEX?
De functie INDEX kan informatie tonen die op het kruispunt van een rij en een kolom in een tabel staat. Deze functie is als volgt opgebouwd:
INDEX(matrix;rij_getal;kolom_getal)
In gewone taal staat hier dat de waarde die je zoekt in een tabel staat (‘matrix’) en dat Excel wil weten in welk rij (‘rij_getal’) en welke kolom (‘kolom_getal’) van die tabel moet worden gezocht.
Laat ik weer beginnen met een sterk vereenvoudig voorbeeld.
Voorbeeld 3 – Tab ‘INDEX’
Dezelfde tabel met Apple iPad’s (nu in kolom G!) en bijbehorende prijzen voor alle Wi-Fi modellen. Ik wil dat Excel mij in de gele cel de prijs uit cel C5 toont. Uitgaande van tabel A1:G7 is dit rij 5 en kolom 3
Formule J3
=INDEX(A1:G7;5;3)
Natuurlijk weet je in de praktijk vooraf niet in welke rij (= welke iPad) en kolom (= geheugen) Excel moet zoeken. Dit zijn variabelen die de gebruiker van de spreadsheet zelf gaat invullen. Deze rij- en kolomnummers zullen dus met behulp van een formule tot stand moeten komen. Ik gebruik daarvoor de functie VERGELIJKEN.
Wat doet de functie VERGELIJKEN?
De functie VERGELIJKEN zoekt een specifieke waarde en geeft aan in welke rij of kolom deze waarde staat. Deze functie is als volgt opgebouwd:
VERGELIJKEN(zoekwaarde;zoeken-matrix;[criteriumtype_getal])
In gewone taal staat hier dat de waarde die je zoekt (‘zoekwaarde’) in een tabel (‘zoeken-matrix’) wordt opgezocht en dat Excel wil weten of de zoekwaarde exact gevonden moet worden (‘criteriumtype_getal’=0).
Voorbeeld 4 – TAB ‘VERGELIJKEN’
Dezelfde tabel met Apple iPad’s en bijbehorende prijzen voor alle Wi-Fi modellen. Ik wil dat Excel mij in gele cel J4 toont in welke rij de iPad Pro 12.9-inch staat. In gele cel J7 moet Excel tonen in welke kolom de 512 GB modellen staan.
Formule J4
=VERGELIJKEN(J3;G3:G7;0)
Formule J7
=VERGELIJKEN(J6;A2:F2;0)
Formule J4 gebruikt de zoekwaarde in cel J3 en vergelijkt deze met de ‘zoekmatrix’ waarin producten staan (G3:G7). De gezochte iPad staat als 2e in de rij. Het rijnummer is 2.
Formule J7 gebruikt de zoekwaarde in cel J6 en vergelijkt deze met de ‘zoekmatrix’ waarin geheugens staan (A2:F2). De gezochte 512 GB staat in de 5e kolom; het kolomnummer is 5.
Als je nu de indexformule =INDEX(A3:F7;J4;J7) zou programmeren, zou deze als uitkomst de prijs in cel E4 geven (€ 1.449). Cel E4 bevindt zich immers in de 2e rij en 5e kolom van matrix A3:F7.
INDEX en VERGELIJKEN combineren
Als je al deze kennis combineert, kun je Excel met behulp van de 2 besproken functies in 1 formule laten zoeken naar de gewenste waarde. Een voorbeeld op basis van het bovenstaande ziet er als volgt uit.
Voorbeeld 5 – Tab ‘INDEX VERGELIJKEN 1’
Dezelfde tabel met Apple iPad’s en bijbehorende prijzen voor alle Wi-Fi modellen. Ik wil dat Excel mij in de gele cel J5 de prijs voor het gekozen product (J3) en geheugen (J4) toont.
Formule J5
=INDEX(A3:F7;VERGELIJKEN(J3;G3:G7;0);VERGELIJKEN(J4;A2:F2;0))
In formule J5 komt alles samen. Je kunt hem als volgt ontleden:
INDEX(A3:A7;…;…) = Toont de waarde die staat op het kruispunt van een specifieke rij en kolom in tabel A3:A7, daar staan immers de prijzen.
VERGELIJKEN(J3;G3:G7;0) = Zoekt in tabel G3:G7 naar het product dat exact overeenkomt met het product in cel J3 en gebruikt dat specifieke rijnummer in de INDEX functie.
VERGELIJKEN(J4:A2:F2;0) = Zoekt in tabel A2:F2 naar het geheugen dat exact overeenkomt met het geheugen in cel J4 en gebruikt dat specifieke kolomnummer in de INDEX functie.
Het gevonden rijnummer is 4, het gevonden kolomnummer is 3 en de INDEX functie toont dus de waarde uit rij 4 en kolom 3 van tabel A3:A7 (= cel C6). Deze waarde bedraagt €489.
Tot slot een aantal tips
- Oefen eerst met de VERGELIJKEN functie zodat je begrijpt hoe rij- en kolomnummers tot stand komen.
- Daarna oefen je met de INDEX functie zodat je begrijpt hoe een specifieke waarde op het kruispunt van een rij en kolom wordt gevonden.
- Vervolgens combineer je de INDEX en VERGELIJKEN functies zodat je gericht een waarde kunt zoeken in een tabel.
- Tenslotte leer je jezelf aan om de HORIZ.ZOEKEN en VERT.ZOEKEN altijd te vervangen door INDEX en VERGLIJKEN functies. Na een paar keer weet je niet beter!