Index Match in Excel: een betere manier om gegevens op te zoeken

INDEX MATCH, een combinatie van de twee Excel-functies INDEX() en MATCH(), is een alternatief voor de veelgebruikte functie VLOOKUP().

In deze gids laat ik je zien hoe INDEX() en MATCH() afzonderlijk en samen werken, hoe je verticale en horizontale opzoekingen uitvoert, en best practices voor het gebruik van INDEX MATCH met praktische voorbeelden.

Wat is INDEX MATCH in Excel?

INDEX MATCH is een korte manier om te verwijzen naar het nesten van de twee functies. Het komt neer op het idee van INDEX(MATCH()). Laten we bekijken hoe beide afzonderlijk en samen werken.

De functie INDEX() in Excel

De functie INDEX() in Excel geeft een waarde of een verwijzing naar een waarde terug uit een bereik of tabel. Je kunt haar op twee manieren gebruiken: arrayvorm en verwijzingsvorm.

Arrayvorm

Je kunt de arrayvorm gebruiken wanneer je werkt met een vaste tabel of array. In dit formaat haalt INDEX() een waarde op op basis van het rij- en kolomnummer dat je opgeeft. De syntaxis is:

INDEX(array, row_num, [column_num])Mogelijk gemaakt door

Laten we deze argumenten begrijpen:

• array (verplicht) is het bereik of de array met waarden waarmee je werkt.

• row_num (verplicht) is het rijnummer waaruit je een waarde wilt retourneren.

• column_num (optioneel) is het kolomnummer waaruit je een waarde wilt retourneren.

We moeten minimaal row_num of column_num opgeven, zodat Excel de waarde kan lokaliseren.

Hier zijn echter enkele snelle aandachtspunten om in gedachten te houden bij deze syntaxis:

• Als de array slechts één rij of één kolom heeft, kun je row_num of column_num weglaten.

• Als zowel row_num als column_num zijn opgegeven, geeft INDEX de waarde terug waar ze elkaar kruisen.

• Als je row_num of column_num op 0 zet, geeft Excel de hele kolom of rij terug (je moet dit als een arrayformule invoeren om het resultaat te zien).

Verwijzingsvorm

Je kunt de verwijzingsvorm gebruiken wanneer je met meerdere bereiken werkt en moet aangeven naar welk bereik gekeken moet worden.

De syntaxis is:

INDEX(reference, row_num, [column_num], [area_num])Mogelijk gemaakt door

Laten we deze argumenten begrijpen:

• reference (verplicht) wordt gebruikt om naar één of meer celbereiken te verwijzen.

• row_num (verplicht) is het rijnummer in het geselecteerde gebied.

• column_num (optioneel) is het kolomnummer in het geselecteerde gebied.

• area_num (optioneel) vertelt Excel welk bereik te gebruiken als er meer dan één is.

Hier zijn nog enkele punten die je moet onthouden:

• Als area_num wordt weggelaten, gebruikt Excel standaard het eerste bereik.

• Je kunt in deze vorm geen bereiken van verschillende werkbladen mixen; dat levert een fout op.

• Als zowel rij als kolom worden weggelaten, wordt het volledige opgegeven gebied geretourneerd.

De functie MATCH() in Excel

De MATCH()-functie helpt je de positie van een waarde in een rij of kolom te vinden. In plaats van de waarde zelf te retourneren, vertelt ze je waar die in de lijst staat.

De syntaxis is:

MATCH(lookup_value, lookup_array, [match_type])Mogelijk gemaakt door

Laten we deze argumenten begrijpen:

• lookup_value (verplicht) is de waarde die je wilt vinden. Dit kan een getal, tekst, logische waarde (TRUE/FALSE) of een verwijzing naar een cel zijn.

• lookup_array (verplicht) is het celbereik waar Excel doorheen zoekt.

• match_type (optioneel) bepaalt hoe Excel zoekt naar de match. De standaardwaarde is 1. Hierbij zoekt 1 de grootste waarde die kleiner dan of gelijk is aan lookup_value (vereist oplopende volgorde). 0 zoekt de exacte overeenkomst (elke volgorde). -1 zoekt de kleinste waarde die groter dan of gelijk is aan lookup_value (vereist aflopende volgorde).

De functie MATCH is niet hoofdlettergevoelig, dus behandelt ze hoofd- en kleine letters hetzelfde. Als je met tekst werkt en wildcardtekens zoals * (een willekeurig aantal tekens) of ? (één teken) wilt gebruiken, zorg dan dat je match_type op 0 zet. Want dat is de enige modus die wildcardzoekopdrachten ondersteunt.

Hoe werken INDEX() en MATCH() samen in Excel?

Een van de meest voorkomende toepassingen van MATCH() is in combinatie met de functie INDEX(). Terwijl MATCH() de positie van een waarde vindt, haalt INDEX() de waarde op die op die positie staat. 

Door MATCH() te nesten binnen INDEX() krijg je een formule die dynamisch en robuust is wanneer kolommen of rijen in de loop van de tijd verschuiven. 

Stel, ik heb een voorbeeldgegevensset. En als ik wil achterhalen wat er in dezelfde rij in kolom B staat als Banaan in kolom A, dan kan ik INDEX MATCH samen zo gebruiken:

=INDEX(B2:B5, MATCH("Banana", A2:A5, 0))Mogelijk gemaakt door

Hier vindt MATCH("Banana", A2:A5, 0) de positie van Banana in kolom A en INDEX(B2:B5, ...) retourneert de waarde uit dezelfde rij in kolom B.

INDEX MATCH vs. VLOOKUP(): welke moet je gebruiken?

INDEX MATCH kan taken uitvoeren die VLOOKUP() niet kan. Maar de keuze hiertussen hangt af van de flexibiliteit en prestaties die je in je Excel-sheets nodig hebt. Laten we ze vergelijken.

Kolomflexibiliteit en gegevensintegriteit

Een van de grootste nadelen van VLOOKUP() is de afhankelijkheid van vaste kolomnummers. Als je kolommen invoegt of verplaatst, kan je formule stukgaan.

Neem dit voorbeeld:

Als er een nieuwe kolom wordt toegevoegd tussen kolom A en kolom B, verschuift de derde kolom en geeft de formule onjuiste resultaten terug, zoals je in de onderstaande afbeelding ziet.

=VLOOKUP("Apple", A2:C5, 2, FALSE)Mogelijk gemaakt door

INDEX MATCH voorkomt dit probleem door met gedefinieerde bereiken te werken in plaats van met hardgecodeerde kolomposities:

=INDEX(B2:B5, MATCH("Apple", A2:A5, 0))Mogelijk gemaakt door

Ook als kolommen worden herschikt, blijft deze formule effectief, wat haar betrouwbaarder en makkelijker te onderhouden maakt. Hier is een kolom vóór kolom B ingevoegd; de formule wordt automatisch bijgewerkt en de resultaten blijven correct. Dit is de kracht van INDEX MATCH.

Zoekrichting en veelzijdigheid

VLOOKUP() kan alleen van links naar rechts zoeken. Dat betekent dat je opzoekkolom altijd de eerste in je bereik moet zijn. Als de waarde die je nodig hebt links van de opzoekkolom staat, kan VLOOKUP() die niet vinden.

Zo werkt deze formule bijvoorbeeld wel als we op fruitnaam zoeken om de kleur rechts te vinden. Als we de kleur van een banaan uit onze lijst willen vinden, geeft die het juiste resultaat: 

Met INDEX MATCH bestaat die beperking niet. Je kunt in elke richting zoeken: links, rechts, omhoog of omlaag. Het ondersteunt ook horizontale opzoekingen, zodat je meer controle hebt over hoe je je gegevens ordent. 

Laten we INDEX MATCH toepassen op dit voorbeeld en zien hoe het zowel links- als rechtszoekingen mogelijk maakt.

Voor links zoeken kun je de volgende formule gebruiken, die de juiste output geeft: 

=INDEX(A2:A5, MATCH(“yellow”, B2:B5,0))Mogelijk gemaakt door

Voor rechts zoeken kun je de volgende formule gebruiken, die het juiste resultaat geeft: 

=INDEX(B2:B5, MATCH(“Banana”,A2:A5,0))Mogelijk gemaakt door

Prestaties en efficiëntie in Excel

VLOOKUP() scant de hele tabel om in één keer een match te vinden en een waarde op te halen. Maar INDEX MATCH splitst de taak: MATCH() vindt de rij of kolom en INDEX() haalt de waarde op. 

Dit maakt het sneller en geheugenefficiënter, vooral in complexe of grote werkmappen.

Hier is een tabel om je een beter idee te geven van welke functie welke functionaliteit ondersteunt:

Wanneer gebruik je INDEX MATCH

Je kunt INDEX MATCH gebruiken wanneer:

• Je met grote datasets werkt waarbij snelheid belangrijk is
• Je model dynamisch is, met kolommen die worden toegevoegd of herschikt
• Je waarden naar links of in horizontale bereiken moet opzoeken

Als je Excel-bestanden klein en eenvoudig zijn, kan VLOOKUP() nog steeds volstaan. Maar voor geavanceerde modellering en rapportage is INDEX MATCH een veiligere keuze.

INDEX MATCH stap voor stap gebruiken

Stel dat je twee kolommen hebt: Fruit en Verkoop. Je wilt de verkoopwaarde voor “Apple” vinden.

Typ hiervoor de gecombineerde INDEX MATCH-formule en druk op Enter:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Mogelijk gemaakt door

Hier vindt MATCH("Apple", A2:A6, 0) de positie van “Apple” in de Fruit-lijst. En INDEX(B2:B6, ...) gebruikt die positie om de bijbehorende verkoopwaarde uit de kolom Verkoop terug te geven.

Je ziet dat de formule 12000 retourneerde, de verkoopwaarde voor Apple. Dat is dus je resultaat.

INDEX MATCH-fouten voorkomen

Hoewel INDEX MATCH een sterke combinatie is, kunnen er toch fouten optreden als je het niet goed instelt. Zo los je de meest voorkomende problemen op en vermijd je fouten die je formules kunnen breken.

#N/A-fouten en hoe je ze oplost

De fout #N/A betekent dat Excel de gezochte waarde niet kon vinden. Veelvoorkomende oorzaken kunnen zijn:

• Typfouten, extra spaties of niet-overeenkomende tekst (bijv. "apple" vs. "Apple")

• Geen waarde in het opzoekbereik

• Verkeerde match_type, bijvoorbeeld 1 in plaats van 0 voor een exacte overeenkomst

• Verborgen tekens (zoals niet-afbrekende spaties) in de gegevens

Er zijn twee manieren om dit op te lossen:

Gebruik TRIM() om leidende of afsluitende spaties op te schonen:

=MATCH(TRIM("Apple"), A2:A6, 0)Mogelijk gemaakt door

Voeg IFERROR() toe om een terugvalwaarde te tonen in plaats van een fout:

=IFERROR(INDEX(B2:B6, MATCH("Apple", A2:A6, 0)), "Not found")Mogelijk gemaakt door

#REF!-fouten in INDEX MATCH-formules

De fout #REF! wijst meestal op een kapotte verwijzing—vaak door:

• Een mismatch tussen het aantal rijen in je INDEX()- en MATCH()-bereiken

• Verwijderde rijen of kolommen die in je formule worden gebruikt

• Formules kopiëren en plakken zonder celverwijzingen bij te werken

Maar er zijn eenvoudige manieren om dit op te lossen: 

• Zorg dat de bereiken van INDEX() en MATCH() even groot zijn:

=INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Mogelijk gemaakt door

• Vergrendel bereiken met absolute verwijzingen (zoals $A$2:$A$6) om formules stabiel te houden tijdens kopiëren-plakken.

• Vermijd het verwijderen van cellen waarvan formules afhankelijk zijn.

Trage prestaties en grote datasets

Als je met duizenden rijen werkt, kunnen niet-geoptimaliseerde INDEX MATCH-formules Excel vertragen.

Zo kun je optimaliseren:

• Beperk opzoekbereiken in plaats van hele kolommen te verwijzen:

=INDEX(B2:B1000, MATCH("Apple", A2:A1000, 0))Mogelijk gemaakt door

• Vermijd vluchtige functies zoals OFFSET(), INDIRECT() en NOW() in de buurt van je opzoekingen.

• Gebruik Excel-tabellen of benoemde bereiken om overzicht te houden en prestaties te verbeteren.

Spill-fouten beheren in Excel 365

Excel 365 introduceert dynamische arrays, die resultaten kunnen “overlopen” naar aangrenzende cellen. Als er iets misgaat in dat proces, krijg je een #SPILL!-fout.

Veelvoorkomende oorzaken kunnen zijn:

• Aangrenzende cellen zijn niet leeg.
• De formule retourneert meerdere waarden in een geblokkeerd of samengevoegd celbereik.
• Je gebruikt een dynamische array waar slechts één waarde nodig is.

Maar deze problemen zijn op te lossen. Zo kun je ze verhelpen:

• Maak de cellen onder of naast de formule leeg.
• Haal samengevoegde cellen in het spill-gebied uit elkaar.
• Als je maar één waarde wilt, forceer dan één resultaat met @:

=@INDEX(B2:B6, MATCH("Apple", A2:A6, 0))Mogelijk gemaakt door

Tot slot

Als je tot nu toe vertrouwt op VLOOKUP(), is dit het moment om over te stappen op iets stabielers. Begin met een paar eenvoudige opzoekingen met INDEX MATCH in een kopie van je werkbestand. Probeer de nieuwe formule in te zetten op plekken waar kolomwijzigingen fouten veroorzaken of waar je datastructuur flexibiliteit vereist. Zodra je ziet hoe soepel die gevallen worden afgehandeld, wil je niet anders meer.