INDEX MATCH in Excel: formule, voorbeelden en veelvoorkomende fouten
By Mirko Peters · · Updated
INDEX MATCH in Excel uitgelegd: de formule, hoe elke functie werkt, opzoeken naar links en in twee richtingen, meerdere criteria en veelvoorkomende fouten, plus een vergelijking met VLOOKUP.
Automatically translated with AI from the original post. Read the English original
INDEX MATCH in Excel is een opzoekformule die uit twee functies bestaat: =INDEX(return_range; MATCH(lookup_value; lookup_range; 0)). MATCH bepaalt waar je waarde in een lijst staat, en INDEX haalt het item op dezelfde positie uit een andere lijst op. Dit artikel is bedoeld voor Excel-gebruikers die VLOOKUP-formules schrijven en tegen de grenzen ervan aanlopen, of die een werkmap met veel INDEX MATCH hebben geërfd en willen begrijpen hoe het werkt. Je ziet elke functie apart, hoe ze samenwerken, hoe je naar links zoekt, hoe je in twee richtingen opzoekt, hoe je twee criteria gebruikt en wat de fouten betekenen.
Belangrijkste punten
- INDEX MATCH bestaat uit twee functies in één formule: =INDEX(return_range; MATCH(lookup_value; lookup_range; 0)). MATCH zoekt de positie en INDEX geeft het item op die positie terug.
- Gebruik 0 als derde argument van MATCH voor een exacte overeenkomst. Laat je het staan op de standaardwaarde 1, dan gaat MATCH ervan uit dat de gegevens oplopend zijn gesorteerd.
- Omdat het opzoekbereik en het retourbereik gescheiden zijn, kan INDEX MATCH links van de opzoekkolom zoeken, wat VLOOKUP niet kan.
- Voor twee voorwaarden kun je de kolommen samenvoegen in een hulpkolom, of een formule gebruiken die twee vergelijkingen binnen MATCH vermenigvuldigt.
- Plaats de formule binnen IFNA om #N/B te vervangen door een melding, terwijl andere fouten wel zichtbaar blijven. Zo worden echte fouten niet verborgen.
Je vindt INDEX MATCH in veel oudere werkmappen, omdat het in elke versie van Excel werkte, lang voordat XLOOKUP bestond. Dat geldt nog steeds, en dat is de belangrijkste reden om het te leren. De details hieronder komen van de Microsoft-pagina's over MATCH, INDEX en VLOOKUP.
Wat INDEX doet
INDEX geeft een waarde terug op basis van een positie binnen een bereik. Microsoft beschrijft de matrixvorm als INDEX(array; rij_getal; [kolom_getal]).
arrayis het bereik waaruit de waarde wordt gehaald.rij_getalis de rij binnen dat bereik.kolom_getalis de kolom binnen dat bereik. Als het bereik maar één kolom heeft, kun je dit weglaten.
De getallen tellen vanaf de linkerbovencel van het bereik, niet vanaf het werkblad. Het voorbeeld van Microsoft gebruikt een blok met Appels en Bananen in de eerste kolom en Citroenen en Peren in de tweede. =INDEX(A2:B3;2;2) geeft Peren terug, en =INDEX(A2:B3;2;1) geeft Bananen terug.
Twee regels van dezelfde pagina zijn het onthouden waard. Als rij_getal of kolom_getal buiten het bereik valt, geeft INDEX een #VERWIJZING!-fout terug. En als je rij_getal of kolom_getal op 0 zet, geeft INDEX de hele kolom of rij terug. In het huidige Microsoft 365 bevestig je gewoon met Enter. In oudere versies voer je de formule in als matrixformule met Ctrl+Shift+Enter.
Op zichzelf heeft INDEX de positie al nodig. Daar komt MATCH om de hoek kijken.
Wat MATCH doet
MATCH zoekt in een bereik en geeft een positie terug. Microsoft geeft de syntaxis als MATCH(lookup_value; lookup_array; [match_type]). Het geeft niet het item zelf terug, alleen de relatieve positie ervan in het bereik.
Het derde argument bepaalt hoe er wordt gezocht:
0zoekt de eerste exacte overeenkomst. Het bereik kan in willekeurige volgorde staan. Dit is degene die je het vaakst gebruikt.1, dat ook de standaard is als je het argument weglaat, zoekt de grootste waarde die kleiner is dan of gelijk is aan de zoekwaarde. Het bereik moet oplopend gesorteerd zijn.-1zoekt de kleinste waarde die groter is dan of gelijk is aan de zoekwaarde. Het bereik moet aflopend gesorteerd zijn.
De voorbeelden van Microsoft gebruiken de waarden 25, 38, 40 en 41 in B2:B5. =MATCH(39,B2:B5,1) geeft 2, de positie van 38, de eerstvolgende lagere waarde. =MATCH(41,B2:B5,0) geeft 4. En =MATCH(40,B2:B5,-1) geeft #N/A, omdat die waarden niet aflopend gesorteerd zijn.
Nog wat gedrag uit de pagina: tekstvergelijking maakt geen onderscheid tussen hoofd- en kleine letters, dus "apple" en "APPLE" zijn hetzelfde. Bij matchtype 0 en een tekstzoekwaarde past ? op elk willekeurig teken en * op elke reeks tekens. Een tilde voor een van beide zoekt naar het letterlijke teken. Als MATCH niets vindt, geeft het #N/A.
De standaardwaarde 1 brengt mensen vaak in de problemen. Als je het derde argument vergeet, gaat MATCH er stilzwijgend van uit dat de gegevens gesorteerd zijn, en kan het zonder foutmelding een verkeerde positie teruggeven. Typ bij bijna elke zoekactie op ID's, namen of codes de 0.
Zet ze samen
Het patroon is altijd hetzelfde: MATCH staat binnen INDEX en levert het rijnummer.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Neem een blad met medewerkers-ID's in A2:A100, namen in B2:B100 en afdelingen in C2:C100. Je typt een ID in F2 en wilt de afdeling.
- Klik op de cel voor het resultaat, bijvoorbeeld G2.
- Typ
=INDEX(C2:C100,om aan te geven waar het antwoord vandaan komt. - Voeg
MATCH(F2,A2:A100,0))toe om aan te geven waar het ID gezocht moet worden. - Druk op Enter.
De uiteindelijke formule is =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH zoekt F2 in A2:A100 en geeft bijvoorbeeld 14. INDEX geeft vervolgens het 14e item in C2:C100 terug.
Houd de bereiken even lang en laat ze op dezelfde rij beginnen. Als MATCH zoekt in A2:A100 en INDEX leest C3:C101, wijst de positie naar de verkeerde rij en is het antwoord stilzwijgend fout. Gebruik voor beide dezelfde begin- en eindrijen, en vergrendel ze met dollartekens, $A$2:$A$100, als je de formule naar beneden wilt kopiëren.
Naar links zoeken, en wat er verandert ten opzichte van VLOOKUP
De VLOOKUP-pagina van Microsoft geeft de syntaxis als VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Er staat dat de zoekwaarde in de eerste kolom van het bereik moet staan, en dat als range_lookup wordt weggelaten, er een benaderende overeenkomst wordt gezocht, waarbij gesorteerde gegevens worden verondersteld. De pagina raadt ook aan XLOOKUP te proberen.
Hier is dezelfde klus op twee manieren, met ID's in kolom A en namen in kolom B:
=VLOOKUP(F2,A2:B100,2,FALSE)=INDEX(B2:B100,MATCH(F2,A2:A100,0))
INDEX MATCH heeft twee echte voordelen. Ten eerste kan het naar links zoeken. Als de namen in kolom B staan en je het ID uit kolom A terug wilt, wissel je gewoon de bereiken om: =INDEX(A2:A100,MATCH(F2,B2:B100,0)). VLOOKUP kan dat niet, want de zoekwaarde moet in de eerste kolom staan. Ten tweede verwijst het direct naar de kolom met het resultaat, in plaats van te tellen. In een VLOOKUP betekent de 2 in de formule "tweede kolom van het bereik". Voeg je een kolom toe binnen het bereik, dan wijst het getal naar een andere kolom. Bij INDEX MATCH past de verwijzing zich aan, net als elke andere verwijzing.
Je leest ook dat INDEX MATCH sneller is. De pagina's van Microsoft beweren dat niet, en een eventueel verschil is alleen relevant in zeer grote werkmappen. Kies het dus niet vanwege de snelheid, maar vanwege de flexibiliteit.
Opzoeken in twee richtingen
Met een opzoeking in twee richtingen vind je de waarde waar een rij en een kolom elkaar kruisen, bijvoorbeeld de verkoop van één product in één maand. Gebruik twee MATCH-functies, één voor de rij en één voor de kolom:
=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))
Hier bevat B2:M20 de getallen, A2:A20 de productnamen, B1:M1 de maandkoppen, en P1 en Q1 bevatten het product en de maand die je zoekt. De eerste MATCH geeft de rij, de tweede geeft de kolom, en INDEX geeft het getal terug waar die twee elkaar kruisen. Let op: het retourbereik begint in B2, niet in A1, zodat de posities overeenkomen met de labelbereiken. Beide MATCH-bereiken beginnen ook in dezelfde rij of kolom als de getallen.
Zoeken op twee criteria
MATCH neemt één zoekwaarde. Als een record alleen met twee velden is te identificeren, zoals categorie en status, heb je twee opties.
Een hulpkolom. Voeg een kolom toe die de twee velden samenvoegt, bijvoorbeeld =A2&"|"&B2 in D2, en vul die naar beneden door. Zoek vervolgens op de samengevoegde tekst:
=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))
Het scheidingsteken is belangrijk. Zonder dat worden "AB" plus "C" en "A" plus "BC" allebei "ABC". De hulpkolom is makkelijk te lezen en na te controleren, en hij werkt in elke versie.
Een matrixformule. Zonder hulpkolom kun je twee vergelijkingen met elkaar vermenigvuldigen:
=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))
Elke vergelijking geeft WAAR of ONWAAR. Door te vermenigvuldigen wordt het alleen 1 waar beide waar zijn, en MATCH zoekt naar de eerste 1. Dit staat niet op Microsofts pagina's over INDEX en MATCH, dus test het op een klein bereik. In oudere versies van Excel moet je de formule mogelijk invoeren met Ctrl+Shift+Enter, zoals Microsoft beschrijft voor matrixresultaten van INDEX. In het huidige Microsoft 365 is Enter voldoende.
Jokertekens en dichtstbijzijnde waarden
Met matchtype 0 en tekst accepteert MATCH jokertekens. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) geeft het item naast de eerste invoer die begint met "Jan". Dat is handig voor gedeeltelijke codes. Houd je patroon specifiek, want MATCH stopt bij de eerste treffer.
Voor indelingen zoals belastingschijven of commissiestaffels gebruik je bewust het standaard matchtype 1, met drempelwaarden die oplopend zijn gesorteerd. Zet 0, 1000 en 5000 in A2:A4 en de tarieven in B2:B4. Dan geeft =INDEX(B2:B4,MATCH(E2,A2:A4,1)) het tarief van de hoogste drempelwaarde die niet groter is dan E2. Dit is een van de weinige situaties waarin de gesorteerde standaard de juiste keuze is.
Fouten oplossen
- #N/A. MATCH heeft niets gevonden. Zoek naar typefouten, spaties aan het eind en getallen die als tekst zijn opgeslagen, want "100" en 100 zijn verschillend. Controleer of het matchtype 0 is, en of gesorteerde matchtypes (1 of -1) echt gesorteerde gegevens hebben.
- #REF!. INDEX vroeg om een rij of kolom buiten het bereik. Dit gebeurt ook als je een rij of kolom verwijdert die de formule gebruikt. Controleer of het MATCH-bereik en het INDEX-bereik even groot zijn.
- Een verkeerd antwoord zonder foutmelding. Meestal een ontbrekende 0 in MATCH, of bereiken die in verschillende rijen beginnen.
- Een verkeerd antwoord na het doorkopiëren. De bereiken zijn verschoven omdat ze niet vergrendeld waren. Voeg
$toe aan de bereiken.
Om een fout door een melding te vervangen, geeft de IFNA-functie van Microsoft een waarde terug die jij kiest wanneer een formule #N/A geeft, en laat hij alle andere fouten ongewijzigd door:
=IFNA(INDEX(C2:C100,MATCH(F2,A2:A100,0)),"Niet gevonden")
Gebruik bij opzoekingen liever IFNA dan IFERROR. IFERROR verbergt elke fout, ook een #REF! door een kapot bereik, waardoor een echt probleem eruitziet als een gewoon "Niet gevonden". IFNA dekt alleen het geval dat je verwacht. Microsoft vermeldt IFNA voor Excel 2016 en later.
INDEX MATCH of XLOOKUP
Als iedereen die je bestand opent de huidige Microsoft 365 heeft, doet XLOOKUP hetzelfde werk met één functie en een ingebouwde melding voor niet-gevonden waarden. We behandelen dat in onze gids over XLOOKUP in Excel. Maar de eigen XLOOKUP-pagina van Microsoft is niet duidelijk over Excel 2016 en 2019, en daar staat ook Excel voor het web niet bij. INDEX MATCH werkt in al die versies. De keuze is dus praktisch: gebruik XLOOKUP in bestanden voor actuele versies, en INDEX MATCH wanneer het bestand naar mensen met oudere versies gaat of wanneer je een werkmap onderhoudt die er al gebruik van maakt.
Veelgemaakte fouten
- De
0in MATCH weglaten en op de standaardinstelling vertrouwen. - Bereiken van verschillende grootte of met verschillende startrijen gebruiken voor INDEX en MATCH.
- Vergeten bereiken met
$te vergrendelen voordat je de formule doorkopieert. - Criteria samenvoegen zonder scheidingsteken in een hulpkolom.
- Alles in IFERROR zetten, zodat kapotte formules eruitzien als ontbrekende gegevens.
- INDEX MATCH geeft verwacht alle overeenkomsten terug. Het geeft er echter alleen de eerste terug.
Gerelateerde onderwerpen om verder te verkennen
- XLOOKUP. De nieuwere zoekfunctie, met ingebouwde zoekrichting en een argument voor als er niets wordt gevonden.
- Meerdere resultaten tegelijk. INDEX MATCH geeft alleen de eerste overeenkomst terug. Met de functie FILTER haal je in versies die deze hebben alle overeenkomsten op.
- Tabellen en benoemde bereiken. Gestructureerde verwijzingen en namen maken zoekopdrachten makkelijker te lezen en zorgen ervoor dat het bereik meegroeit met je gegevens.
- Prestaties bij grote werkbladen. Gebruik begrensde bereiken in plaats van hele kolommen, en houd de bereiken strak.
- Hoofdlettergevoelig zoeken. MATCH negeert hoofdletters, dus een hoofdlettergevoelige zoekopdracht vraagt om een andere aanpak. Test elke workaround voordat je erop vertrouwt.
Volgende stap
Herschrijf een van je VLOOKUP-formules als INDEX MATCH en vergelijk het resultaat bij een paar bekende waarden. Probeer daarna dezelfde formule met een zoekopdracht naar links. Als je beide hebt gedaan, weet je wanneer INDEX MATCH in Excel het betere hulpmiddel is voor de klus.
Bronnen
- MATCH-functie (Microsoft Support)
- INDEX-functie (Microsoft Support)
- VLOOKUP-functie (Microsoft Support)
- IFNA-functie (Microsoft Support)
- XLOOKUP-functie (Microsoft Support)
Veelgestelde vragen
Hoe werkt INDEX MATCH in Excel?
MATCH zoekt in een bereik naar je zoekwaarde en geeft de positie ervan terug, bijvoorbeeld 5 voor het vijfde item. INDEX gebruikt dat getal vervolgens om het item op dezelfde positie in een ander bereik terug te geven. Samen levert =INDEX(C2:C100;MATCH(F2;A2:A100;0)) dus de waarde uit kolom C op die bij F2 hoort, waarbij F2 in kolom A wordt gezocht.
Is INDEX MATCH beter dan VLOOKUP?
Het is flexibeler. VLOOKUP vereist dat de zoekwaarde in de eerste kolom van het bereik staat en telt kolommen op basis van een getal, terwijl INDEX MATCH twee afzonderlijke bereiken gebruikt. Daardoor kan het naar links zoeken en wordt het niet beïnvloed door ingevoegde kolommen. Voor een eenvoudige opzoeking naar rechts werkt beide. XLOOKUP is in nieuwere versies een derde optie.
Waarom geeft INDEX MATCH #N/B terug?
MATCH geeft #N/B terug wanneer het geen overeenkomst vindt. Controleer op typefouten, extra spaties, getallen die als tekst zijn opgeslagen en het type overeenkomst. Gebruik 0 voor een exacte overeenkomst. Met de standaardwaarde 1 moet het opzoekbereik oplopend zijn gesorteerd, en bij -1 aflopend.