Het Excel bestand dat hoort bij deze pagina over INDEX en VERGELIJKEN, staat onderaan de tekst en mag gratis worden gedownload voor eigen gebruik.
Voorbeeldtabel
Om uit te kunnen leggen hoe de functies INDEX en VERGELIJKEN het zoekwerk in complexere tabellen vereenvoudigen, neem ik de volgende tabel als uitgangspunt.
In kolom A tref je verschillende iPad’s aan die Apple verkoopt. In de kolommen B tot en met J zie je vervolgens fictieve aantallen iPad’s per maand. Het gaat om gebudgetteerde aantallen (kolom B, E en H), voorspelde aantallen (kolom C, F en I) en werkelijk verkochte aantallen producten (kolom D, G en J).
In deel 1 heb ik laten zien hoe je op basis van twee variabelen zoekt in een tabel (product, geheugenomvang). In deze tabel wil ik kunnen zoeken op basis van het product, het aantallen-type (budget, forecast, werkelijk) én de maand. Dit betekent dat ik in de INDEX en VERGELIJKEN functies drie variabelen moet verwerken.
Er zijn tenminste twee verschillende manieren waarop je drie variabelen kunt verwerken:
1) Gebruikmaken van een hulprij
2) INDEX functie 2x gebruiken
Gebruikmaken van een hulprij
De meest eenvoudige manier om 3 variabelen te verwerken is het aanleggen van een hulprij. Deze gele hulprij maak ik boven de tabel aan (rij 1) en hierin combineer ik het aantallen-type (rij 2) en de maand (rij 3).
Voorbeeld 1 – Tab ‘INDEX VERGELIJKEN HULPRIJ’
In gele cel B13 wil ik het verkoopvolume vermelden van een vrij te kiezen product (cel B10), aantallen-type (cel B11) en maand (cel B12). Ik heb een hulprij aangelegd (rij 1) met de onderstaande formules.
Formule B1
=B2&B3
…
Formule J1
=J2&J3
Door het aanleggen van de hulprij kan ik op ‘normale wijze’ de INDEX en VERGELIJKEN functies toepassen in een formule.
Formule B13
=INDEX(B4:J8;VERGELIJKEN(B10;A4:A8;0);VERGELIJKEN(B11&B12;B1:J1;0))
In formule B13 komt alles samen. Je kunt hem als volgt ontleden:
INDEX(B4:J8;…;…) = Toont de waarde die staat op het kruispunt van een specifieke rij en kolom in tabel B4:J8, daar staan immers de aantallen.
VERGELIJKEN(B10;A4:A8;0) = Zoekt in tabel A4:A8 naar het product dat exact overeenkomt met het product in cel B10 en gebruikt dat specifieke rijnummer in de INDEX functie.
VERGELIJKEN(B11&B12;B1:J1;0) = Zoekt in hulprij B1:J1 naar de combinatie van het aantallen-type (B11) en de maand (B12) dat exact overeenkomt en gebruikt dat specifieke kolomnummer in de INDEX functie.
Het gevonden rijnummer is 4, het gevonden kolomnummer is 7 en de INDEX functie toont dus de waarde uit rij 4 en kolom 7 van tabel B4:J8 (= cel H7). Deze waarde bedraagt 180.
INDEX functie 2x gebruiken
Het voordeel van het gebruiken van een hulprij is dat de uiteindelijk formule in B13 redelijk goed leesbaar en begrijpbaar blijft. Echter, een hulprij aanmaken is niet nodig als je de INDEX functie 2x gebruikt in de formule van cel B13.
Voorbeeld 2 – Tab ‘2X INDEX VERGELIJKEN’
In gele cel B12 wil ik wederom het verkoopvolume vermelden van een vrij te kiezen product (cel B9), aantallen-type (cel B10) en maand (cel B11). Dit keer gebruik ik geen hulprij maar zet ik 2x de INDEX functie in.
Formule B13
=INDEX(B3:J7;VERGELIJKEN(B9;A3:A7;0);VERGELIJKEN(B10&B11;INDEX(B1:J1&B2:J2;;);0))
Formule B13 bevat nu 2x de INDEX functie. Je kunt hem als volgt ontleden:
INDEX(B3:J7;…;…) = Toont de waarde die staat op het kruispunt van een specifieke rij en kolom in tabel B3:J7, daar staan immers de aantallen.
VERGELIJKEN(B9;A3:A7;0) = Zoekt in tabel A3:A7 naar het product dat exact overeenkomt met het product in cel B9 en gebruikt dat specifieke rijnummer in de INDEX functie.
VERGELIJKEN(B10&B11;INDEX(B1:J1&B2:J2;;);0) = Zoekt in de combinatie van rij (B1:J1) én (B2:J2) naar de combinatie van het gekozen aantallen-type (B10) en de gekozen maand (B11) dat exact overeenkomt en gebruikt dat specifieke kolomnummer in de INDEX functie.
Het gevonden rijnummer is 2, het gevonden kolomnummer is 2 en de INDEX functie op hoofdniveau toont dus de waarde uit rij 2 en kolom 2 van tabel B3:J7 (= cel C4). Deze waarde bedraagt 950.
Meer dan 3 variabelen
Als je meer dan 3 variabelen hebt, kun je uiteraard weer een hulprij aanmaken waarin je de verschillende criteria combineert met het &-teken (zie boven). In onderstaand voorbeeld gebruik ik opnieuw de INDEX functie in de VERGELIJKEN functie.
Voorbeeld 3 – Tab ‘3X INDEX VERGELIJKEN’
In gele cel B14 wil ik wederom het verkoopvolume vermelden van een vrij te kiezen product (cel B10), jaar (cel B11), aantallen-type (cel B12) en maand (cel B13). Ik gebruik 2x de INDEX functie.
Formule B14
=INDEX(B4:M8;VERGELIJKEN(B10;A4:A8;0);VERGELIJKEN(B11&B12&B13;INDEX(B1:M1&B2:M2&B3:M3;;);0))
Formule B14 bevat wederom 2x de INDEX functie. Je kunt hem als volgt ontleden:
INDEX(B4:M8;…;…) = Toont de waarde die staat op het kruispunt van een specifieke rij en kolom in tabel B4:M8, daar staan immers de aantallen.
VERGELIJKEN(B10;A4:A8;0) = Zoekt in tabel A4:A8 naar het product dat exact overeenkomt met het product in cel B10 en gebruikt dat specifieke rijnummer in de INDEX functie.
VERGELIJKEN(B11&B12&B13;INDEX(B1:M1&B2:M2&B3:M3;;);0) = Zoekt in de combinatie van rij (B1:M1) én (B2:M2) én (B3:M3) naar de combinatie van het gekozen jaar (B11), aantallen-type (B12) en de gekozen maand (B13) dat exact overeenkomt en gebruikt dat specifieke kolomnummer in de INDEX functie.
Het gevonden rijnummer is 2, het gevonden kolomnummer is 5 en de INDEX functie op hoofdniveau toont dus de waarde uit rij 2 en kolom 5 van tabel B4:M8 (= cel F5). Deze waarde bedraagt 1.250.
Tot slot een aantal tips
- Als je niet bekend bent met het gebruik van het &-teken om tekst samen te voegen in een hulprij of in de getoonde formules, verdiep je dan eerst in de formule TEKST.SAMENVOEGEN van Excel.
- Als je graag met een (of meer) hulprij(en) werkt, kun je deze verbergen om je oorspronkelijke tabel overzichtelijk te houden.
- Leer de functie INDEX te lezen als (‘In welke tabel staat de uitkomst die ik zoek?’, ‘In welke rij moet ik zoeken?’, ‘In welke kolom moet ik zoeken?’).
- Als je in rijen of kolommen zoekt op meerdere variabelen en je wilt hulprijen vermijden, gebruik dan de INDEX functie in de VERGELIJKEN functie voor rijen (niet in dit voorbeeld) of kolommen (wel in dit voorbeeld).