Hoe je INDEX MATCH met meerdere criteria gebruikt in Excel

Een paar jaar geleden werkte ik aan een analyse van een marketingcampagne waarbij ik verkoopprestaties over meerdere regio’s moest vergelijken. De gegevens stonden verspreid over meerdere Excel-sheets, en ik moest specifieke productverkoopcijfers in één samenvattend rapport ophalen. In eerste instantie probeerde ik de gegevens handmatig te zoeken en te kopiëren, maar dat was lastiger dan ik dacht. Als er één verkeerde rij in staat, kan het hele rapport in elkaar storten.

Toen ontdekte ik INDEX MATCH(). Het kostte me een paar pogingen om de formule goed te krijgen, maar het werd onderdeel van mijn routine zodra ik zag hoe eenvoudig ik er precies de cijfers mee kon vinden en ophalen die ik nodig had. Met slechts twee functies kon ik exact de gegevens ophalen die ik nodig had, hoe verspreid ze ook stonden over verschillende spreadsheets.

In dit artikel leg ik uit hoe je hetzelfde kunt doen met de functies INDEX() en MATCH(). Er valt altijd meer te leren met Excel. Als je beginner bent, raad ik je onze cursus Introduction to Excel sterk aan. Heb je al meer ervaring, probeer dan onze Advanced Excel Functions-cursus.

Een opfrisser over INDEX MATCH

INDEX MATCH is een korte manier om te verwijzen naar de combinatie van twee Excel-functies die samenwerken om geavanceerde opzoekingen uit te voeren. We zouden dit ook kunnen schrijven als INDEX(MATCH()), maar in dit artikel kies ik voor INDEX MATCH. Laten we nu elk onderdeel afzonderlijk bekijken:

De functie INDEX() haalt de waarde van een cel op op basis van zijn positie binnen een opgegeven bereik. De syntax is als volgt:

=INDEX(array, row_num, [column_num])

Hier:

• array is het celbereik waaruit je een waarde wilt ophalen.

• row_num is het rijnummer in het array waarvan je een waarde wilt retourneren.

• column_num (optioneel) is het kolomnummer in het array waarvan je een waarde wilt retourneren.

De functie MATCH() bepaalt de relatieve positie van een waarde binnen een bereik. De syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

Hier:

• lookup_value is de waarde die je wilt vinden.

• lookup_array is het bereik waarin de functie naar de waarde zoekt.

match_type is optioneel. 1 (standaard) zoekt de waarde die kleiner is dan of gelijk is aan lookup_value (array moet oplopend gesorteerd zijn). 0 zoekt een exacte overeenkomst (array hoeft niet gesorteerd te zijn). -1 zoekt de kleinste waarde die groter is dan of gelijk is aan lookup_value (array moet aflopend gesorteerd zijn).

Hoe INDEX() combineren met MATCH()

Door MATCH() in INDEX() te nesten, kunnen we een dynamische opzoeking maken. Begrijp dit met een voorbeeld: stel dat je de positie van David Wilson in de dataset wilt vinden. In plaats van het rijnummer hard te coderen in INDEX(), gebruik je MATCH() om dit te bepalen:

=INDEX(C2:C6, MATCH("David Wilson", A2:A6, 0))

In de bovenstaande formule retourneert MATCH("David Wilson", A2:A6, 0) 4, wat de rijpositie is. En INDEX(C2:C6, 4) haalt de waarde op uit de 4e rij van het bereik C2:C6, namelijk Seattle.

Combineer INDEX() met MATCH(). Afbeelding door de auteur.

Om dit nog dynamischer te maken, kun je de hardgecodeerde David Wilson vervangen door een celverwijzing. Zo past de formule zich automatisch aan op basis van de waarde in D4:

=INDEX(C2:C6,MATCH(D4,A2:A6,0))

Vervang de hardgecodeerde waarde in INDEX MATCH. Afbeelding door de auteur.

INDEX MATCH vs. VLOOKUP()

Nu je weet hoe INDEX() en MATCH() afzonderlijk werken en hoe de combinatie opzoekingen dynamischer maakt, kijken we waarom INDEX MATCH een betere keuze is dan VLOOKUP().

• In tegenstelling tot VLOOKUP(), dat vereist dat de opzoekkolom links staat, kun je met INDEX MATCH gegevens ophalen uit elke kolom, ongeacht de positie.

• INDEX MATCH verwerkt alleen het benodigde celbereik, terwijl VLOOKUP() hele tabellen scant. 

• Formules met VLOOKUP() kunnen stukgaan als er kolommen worden ingevoegd of verwijderd, omdat ze vertrouwen op statische kolomindexen. INDEX MATCH daarentegen verwijst naar dynamische bereiken, zodat je formules intact blijven ondanks structurele veranderingen in je gegevens.

• Met INDEX MATCH hoeven we kolomnummers niet handmatig te tellen. Geef de opzoekkolom en de retourkolom op, en je bent klaar. 

INDEX MATCH met meerdere criteria

Ik werk vaak met datasets die dubbele vermeldingen bevatten, en daarin waarden vinden is erg lastig. Maar nu gebruik ik INDEX MATCH omdat het dit soort situaties heel eenvoudig afhandelt, in tegenstelling tot andere standaard opzoekformules. Ik neem je stap voor stap mee hoe ik dit doe.

Data inrichten voor meerdere criteria

Maak eerst je dataset en zorg dat die netjes is georganiseerd in een tabel met duidelijke koppen voor elke kolom. Elke rij moet een uniek record vertegenwoordigen en elke kolom een specifiek gegevenstype bevatten.

Hier is bijvoorbeeld een voorbeeld-dataset:

Dataset voor INDEX MATCH met meerdere criteria. Afbeelding door de auteur.

De formule schrijven voor meerdere criteria

Zodra je data goed is georganiseerd, is het tijd om de formule te schrijven. De INDEX MATCH-formule haalt een waarde uit een andere kolom op door een rij te identificeren die aan meerdere voorwaarden voldoet. Dit doe je door logische tests te combineren binnen de functie MATCH() en die te embedden in de functie INDEX().

Dit is de basis-syntax hiervoor: 

{=INDEX(return_range, MATCH(1, (criteria1=range1) * (criteria2=range2) * (…), 0))}

Hier: 

• Return_range is het bereik waaruit de waarde wordt geretourneerd.

• Criteria1, Criteria2, … zijn de voorwaarden waaraan moet worden voldaan.

• Range1, Range2, … zijn bereiken die bij de criteria horen.

Nu we een dataset hebben klaargezet, kijken we van dichtbij naar de twee methoden om onze vraag te beantwoorden: hoe gebruik je INDEX MATCH met meerdere criteria.

Gebruik hulpkolommen voor complexe criteria

Als je dataset meerdere voorwaarden heeft, gebruik dan hulpkolommen om het proces te vereenvoudigen. Daarmee combineer je alle voorwaarden in één kolom, zodat opzoekingen makkelijker worden. Ik gebruik bijvoorbeeld dezelfde dataset om een hulpkolom te maken door de kolommen Voornaam en Salaris te combineren:

=A2&B2

Maak een hulpkolom. Afbeelding door de auteur.

Deze hulpkolom vereenvoudigt mijn INDEX MATCH-formule. In plaats van een complexe matrixformule met meerdere voorwaarden te schrijven, verwijs ik in mijn formule naar de hulpkolom voor een eenvoudigere aanpak:

=INDEX(D2:D11, MATCH("AliceHR", E2:E11, 0))

INDEX MATCH met hulpkolom. Afbeelding door de auteur.

Meerdere criteria combineren met een matrixformule

Als je geen hulpkolommen wilt gebruiken, kun je matrixformules gebruiken om hetzelfde resultaat te bereiken. Daarmee kun je meerdere criteria direct binnen de functie MATCH() evalueren. Zo vind ik bijvoorbeeld het Salaris van Alice in de afdeling HR:

Stap 1: Schrijf de functie MATCH() met logische voorwaarden:

 MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0)

In deze formule zorgt 1 ervoor dat de functie MATCH() zoekt naar rijen waar alle voorwaarden waar zijn. (F4=A2:A11) controleert of de waarde in F4 overeenkomt met een waarde in het bereik A2:A11. (F5=B2:B11) controleert of de waarde in F5 overeenkomt met een waarde in het bereik B2:B11. De operator * werkt als een AND-logica en zorgt ervoor dat aan alle voorwaarden wordt voldaan.

Stap 2: Wikkel deze functie MATCH() in de functie INDEX():

=INDEX(D2:D11, MATCH(1, (F4=A2:A11) * (F5=B2:B11), 0))

Stap 3: Rond de formule af. Gebruik je een oudere versie van Excel, druk dan op Ctrl+Shift+Enter om er een matrixformule van te maken. In nieuwere versies druk je op Enter.

Matrix-INDEX MATCH met meerdere criteria. Afbeelding door de auteur.

Geavanceerde toepassingen voor INDEX MATCH met meerdere criteria

Je kunt nog veel meer doen met de functie INDEX MATCH. Zo dan: 

Gebruik INDEX MATCH met benoemde bereiken en dynamische bereiken

Ik gebruik benoemde bereiken in Excel om betekenisvolle namen te geven zoals results of totalSales in plaats van standaardverwijzingen zoals A1:A10. Zo wordt het makkelijker om formules over verschillende sheets heen te beheren. 

Om een celbereik te benoemen, selecteer je de cellen en druk je op Ctrl + F3 (voor Windows) of Cmd + F3 (voor Mac) om de Name Manager te openen. Klik vervolgens op New, voer een naam in en klik op OK.

Benoem het bereik. Afbeelding door de auteur.

Het enige verschil tussen een benoemd bereik en een dynamisch bereik is dat een benoemd bereik verwijst naar een vaste groep cellen, terwijl een dynamisch bereik zich automatisch aanpast wanneer gegevens worden toegevoegd of verwijderd.

Om een dynamisch bereik in te stellen, selecteer je de cellen. Ga op het tabblad Formulas naar Name Manager of druk op Ctrl + F3 om de Excel-Name Manager te openen en klik op New. Het dialoogvenster New Name verschijnt. Vul nu in het veld Name je gewenste naam in. Voer vervolgens in het veld Refers to de formule in voor het dynamische bereik. 

Stel een dynamisch bereik in. Afbeelding door de auteur. 

Laten we nu een voorbeeld bekijken: ik heb twee dynamische en één statisch bereik gedefinieerd:

• total_amount: =$F$2:INDEX($F:$F, COUNTA($F:$F))

• items_list: =$A$2:INDEX($A:$A, COUNTA($A:$A))

• lookup_value: =$I$3

Nu gebruik ik deze bereiken in de INDEX MATCH-formule:

=INDEX(total_amount,MATCH(lookup_value,items_list,0))

En je ziet dat de formule veel eenvoudiger te begrijpen wordt met duidelijke namen.

Gebruik dynamische en benoemde bereiken met INDEX MATCH. Afbeelding door de auteur.

Geneste INDEX MATCH voor complexe opzoekingen

Naast basiswerk kun je ook geneste INDEX MATCH-functies gebruiken om complexe opzoekingen af te handelen. Ik heb bijvoorbeeld een dataset met verkopen per productcategorie in verschillende regio’s. 

Ruwe dataset. Afbeelding door de auteur.

Ik wil de meubelverkopen in het East vinden. Maar daarvoor moet ik zowel de productcategorie (rij) als de regio (kolom) matchen, wat een basis-INDEX MATCH niet kan. Daarom gebruik ik hier de volgende geneste INDEX MATCH-formule:

=INDEX(B2:D4, MATCH(D6, A2:A4, 0), MATCH(D7, B1:D1, 0))

Zo werkt het: INDEX() haalt een waarde op uit het bereik B2:D4, maar daarvoor is een rijnummer en een kolomnummer nodig om precies te weten waar gezocht moet worden. De eerste MATCH(D6, A2:A4, 0) bepaalt het rijnummer. Als D6 de tekst Furniture bevat, doorzoekt het kolom A2:A4 en vindt dit op de tweede rij.

Vervolgens bepaalt MATCH(D7, B1:D1, 0) het kolomnummer. Als in D7 East staat, kijkt het over B1:D1 en vindt dit in de tweede kolom.

Zodra INDEX() de rij en kolom weet, geeft het de uitvoerwaarde weer. In ons geval zijn de verkopen voor Furniture in het East 450.

Gebruik geneste INDEX MATCH. Afbeelding door de auteur.

Ik gebruik deze formule in plaats van handmatig door rijen en kolommen te zoeken, omdat dit alles precies afhandelt. 

Veelvoorkomende uitdagingen en tips voor probleemoplossing

Toen ik begon met INDEX MATCH, liep ik tegen verschillende uitdagingen aan, en ik wil niet dat jij dezelfde frustraties ervaart. Daarom neem ik je mee door de meest voorkomende problemen en laat ik zien hoe je ze oplost.

Fouten afhandelen in INDEX MATCH-formules

Fouten zoals #N/A en #VALUE! kunnen in het begin frustrerend lijken, maar ze zijn vrij eenvoudig te verhelpen. Zo spoor je de oorzaak op en los je het met simpele stappen op.

De #N/A-fout treedt op wanneer de functie MATCH() geen waarde vindt. Dit komt doordat de opzoekwaarde niet in het zoekbereik voorkomt of doordat de data verborgen spaties bevat. Ik verwees bijvoorbeeld ooit naar de verkeerde kolom bij het ophalen van werknemersnamen:

=INDEX(B2:B6,MATCH(E3,C2:C6,0))

#N/A-fout in INDEX MATCH. Afbeelding door de auteur.

Om dit soort problemen op te lossen, controleer je of de opzoekwaarde in het bereik voorkomt en gebruik je de functie TRIM() om spaties te verwijderen:

=TRIM(INDEX(B2:B6,MATCH(E3,A2:A6,0)))

#N/A-fout verholpen in INDEX MATCH. Afbeelding door de auteur.

#VALUE!-fout verschijnt wanneer de formule niet is ingesteld als matrixformule. Als ik bijvoorbeeld de functie MATCH() gebruik en meer dan één bereik opneem, ziet Excel dit als een matrixformule. Als die niet goed is ingesteld, geeft Excel een #VALUE!-fout.

=INDEX(C2:C6,MATCH(D4&E4,A2:A6&B2:B6,0))

#VALUE-fout in INDEX MATCH. Afbeelding door de auteur.

Om dit op te lossen, druk je op Ctrl + Shift + Enter nadat je de formule hebt ingevoerd. Zo zet Excel de formule tussen accolades {}, wat aangeeft dat het nu een matrixformule is. Typ de accolades niet handmatig, want dan werkt de formule niet.

#VALUE-fout verholpen in INDEX MATCH. Afbeelding door de auteur.

Prestaties optimaliseren met grote datasets

Bij grotere datasets werden mijn formules af en toe traag, waardoor ik moest wachten tot berekeningen waren bijgewerkt. Als je met vergelijkbare problemen kampt, probeer dan deze tips: 

• Beperk het opzoekbereik: Beperk bereiken tot wat echt nodig is. Gebruik bijvoorbeeld in plaats van A:A A1:A100 om de rekentijd te verminderen.

• Gebruik hulpkolommen: Bereken complexe criteria vooraf met hulpkolommen. Zo verminder je de rekenlast van matrixformules.

• Schakel handmatige berekening in: Zet Excel op handmatige berekening om constante herberekeningen te vermijden. Druk na aanpassingen op F9 om formules handmatig bij te werken.

• Vermijd volatiele functies: Gebruik volatiele functies zoals NOW(), RAND() en TODAY() zo min mogelijk in combinatie met INDEX MATCH. Deze functies zorgen voor herberekeningen bij elke werkmapupdate.

Tot slot 

INDEX MATCH-technieken besparen tijd en vereenvoudigen complexe data-analyses. Werk je met enorme datasets, dan zijn ze het proberen waard. Maar de beste manier om je begrip te versterken is door te oefenen. Dus ik zou zeggen: pak een paar datasets en experimenteer met wat je hebt geleerd. Zo heb ik mijn vaardigheden aangescherpt. 

Wil je je kennis verdiepen, bekijk dan onze cursus Advanced Excel Functions om een breder scala aan krachtige tools onder de knie te krijgen. Maar als je een volledige expertise in data-analyse met Excel wilt opbouwen, raad ik onze cursus Data Analysis in Excel aan. Die behandelt alles van datavoorbereiding tot visualisatie.