Excel INDEX MATCH: Basis- en geavanceerde zoekopdrachten

In Excel is het vaak noodzakelijk om specifieke gegevens nauwkeurig op te halen. Hoewel de functies INDEX en MATCH elk hun eigen voordelen hebben, ontstaat er door ze te combineren een krachtig hulpmiddel voor gegevensopzoeking. Samen bieden ze uiteenlopende zoekmogelijkheden, van eenvoudige horizontale en verticale zoekopdrachten tot geavanceerdere functies zoals tweezijdig zoeken, hoofdlettergevoelig zoeken en zoeken op meerdere criteria. In vergelijking met VLOOKUP bieden INDEX en MATCH samen uitgebreidere opties voor gegevensopzoeking. In deze handleiding gaan we dieper in op de mogelijkheden die deze combinatie biedt.

• INDEX-functie
• MATCH-functie
• INDEX en MATCH combineren

• Tweezijdig zoeken
• Links zoeken
• Hoofdlettergevoelig zoeken
• Dichtstbijzijnde overeenkomst zoeken
• Zoeken op meerdere criteria
• Zoeken over meerdere kolommen
• Zoeken naar de eerste niet-lege waarde
• Zoeken naar de eerste numerieke waarde
• Zoeken naar MAX- of MIN-koppelingen
• Tip: Pas je eigen #N/B-foutmeldingen aan

Hoe gebruik je INDEX en MATCH in Excel

Voordat we de functies INDEX en MATCH gaan gebruiken, is het goed om eerst te begrijpen hoe deze functies ons kunnen helpen bij het opzoeken van waarden.

Hoe gebruik je de INDEX-functie in Excel

De INDEX-functie in Excel geeft de waarde terug op een bepaalde locatie binnen een specifiek bereik. De syntaxis van de INDEX-functie is als volgt:

• array (verplicht) verwijst naar het bereik waaruit je de waarde wilt ophalen.
• row_num (verplicht, tenzij column_num is opgegeven) verwijst naar het rijnummer binnen het array.
• column_num (optioneel, maar vereist als row_num wordt weggelaten) verwijst naar het kolomnummer binnen het array.

Stel, je wilt de score van Jeff weten, de6e student op de lijst. Dan kun je de INDEX-functie als volgt gebruiken:

=INDEX(C2:C11,6)

√ Opmerking: Het bereik C2:C11 bevat de scores, terwijl het getal 6 de examenuitslag van de 6e student opzoekt.

Laten we een kleine test doen. Voor de formule =INDEX(A1:C1,2), welke waarde wordt dan teruggegeven? --- Inderdaad, het resultaat is Geboortedatum, de 2e waarde in de opgegeven rij.

Nu weten we dat de INDEX-functie perfect werkt met horizontale of verticale bereiken. Maar wat als je een waarde wilt ophalen uit een groter bereik met meerdere rijen en kolommen? In dat geval geef je zowel een rijnummer als een kolomnummer op. Bijvoorbeeld, om Jeff's score te vinden binnen het tabelbereik in plaats van slechts één kolom, kun je zijn score lokaliseren met rijnummer6 en kolomnummer3 in de cellen van A2 tot en met C11 als volgt:

=INDEX(A2:C11,6,3)

Voor een grote database met veel rijen en kolommen is het niet handig om de formule telkens met een exact rijnummer en kolomnummer in te vullen. Dit is het moment waarop je de MATCH-functie erbij moet gebruiken.

Hoe gebruik je de MATCH-functie in Excel

De MATCH-functie in Excel geeft een numerieke waarde terug, namelijk de positie van een specifiek item binnen het opgegeven bereik. De syntaxis van de MATCH-functie is als volgt:

• lookup_value (verplicht) verwijst naar de waarde die je wilt zoeken in de lookup_array.
• lookup_array (verplicht) verwijst naar het bereik waarin je MATCH wilt laten zoeken.
• match_type (optioneel): 1, 0 of -1.
  • 1(standaard), MATCH zoekt de grootste waarde die kleiner dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten oplopend zijn gesorteerd.
  • 0, MATCH zoekt de eerste waarde die exact gelijk is aan de lookup_value. De waarden in de lookup_array mogen in willekeurige volgorde staan. (Als het match_type op0 staat, kun je ook jokertekens gebruiken.)
  • -1, MATCH zoekt de kleinste waarde die groter dan of gelijk is aan de lookup_value. De waarden in de lookup_array moeten aflopend zijn gesorteerd.

Stel, je wilt de positie van Vera in de naamlijst weten. Dan gebruik je de MATCH-formule als volgt:

=MATCH("Vera",A2:A11,0)

√ Opmerking: Het resultaat “4” geeft aan dat de naam “Vera” op de4e positie in de lijst staat.

Nu we de basis van de INDEX- en MATCH-functies in Excel kennen, kunnen we ze gaan combineren.

Hoe combineer je INDEX en MATCH in Excel

Bekijk het onderstaande voorbeeld om te zien hoe we de functies INDEX en MATCH kunnen combineren:

Om Evelyn’s score te vinden, wetende dat de examencijfers in de 3e kolom staan, kun je de MATCH-functie gebruiken om automatisch de rijpositie te bepalen zonder handmatig te tellen. Vervolgens gebruik je de INDEX-functie om de waarde op het snijpunt van de gevonden rij en de3e kolom op te halen:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Omdat de formule misschien wat ingewikkeld lijkt, lopen we hem stap voor stap door.

De INDEX-formule bevat drie argumenten:

• row_num: MATCH("Evelyn",A2:A11,0)geeft INDEX de rijpositie van de waarde "Evelyn" in het bereik A2:A11, namelijk5.
• column_num:3 geeft aan dat INDEX de waarde in de3e kolom binnen het array moet zoeken.
• array: A2:C11 geeft aan dat INDEX de overeenkomende waarde op het snijpunt van de opgegeven rij en kolom binnen het bereik van A2 tot en met C11 moet teruggeven. Uiteindelijk krijgen we het resultaat90.

In de bovenstaande formule gebruikten we een vaste waarde, "Evelyn". In de praktijk is het echter niet handig om vaste waarden te gebruiken, omdat je deze telkens moet aanpassen als je naar andere gegevens wilt zoeken, bijvoorbeeld de score van een andere student. In zulke gevallen kun je celverwijzingen gebruiken om dynamische formules te maken. In dit voorbeeld vervang ik "Evelyn" door F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

Voorbeelden van INDEX- en MATCH-formules

In dit deel bespreken we verschillende situaties waarin je de functies INDEX en MATCH kunt gebruiken om aan uiteenlopende behoeften te voldoen.

INDEX en MATCH voor tweezijdig zoeken

In het vorige voorbeeld kenden we het kolomnummer en gebruikten we een MATCH-formule om het rijnummer te vinden. Maar wat als we het kolomnummer ook niet zeker weten?

In zulke gevallen kun je een tweezijdige zoekopdracht uitvoeren, ook wel matrixzoekopdracht genoemd, door twee MATCH-functies te gebruiken: één om het rijnummer te vinden en de andere om het kolomnummer te bepalen. Bijvoorbeeld, om Evelyn’s score te vinden, gebruik je de volgende formule:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX en MATCH voor links zoeken

Stel je voor dat je Evelyn's klas wilt bepalen. Je hebt misschien gemerkt dat de kolom met klassen links van de kolom met namen staat, een situatie die niet mogelijk is met de krachtige Excel-functie VLOOKUP.

Het feit dat je naar links kunt zoeken is juist één van de voordelen waarbij de combinatie van INDEX en MATCH beter presteert dan VLOOKUP.

Om Evelyn’s klas te vinden, gebruik je de volgende formule om Evelyn te zoeken in B2:B11 en de bijbehorende waarde uit A2:A11 op te halen.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX en MATCH voor hoofdlettergevoelig zoeken

De MATCH-functie is standaard niet hoofdlettergevoelig. Wil je toch onderscheid maken tussen hoofdletters en kleine letters, dan kun je de EXACT-functie toevoegen. Door MATCH te combineren met EXACT in een INDEX-formule kun je effectief een hoofdlettergevoelige zoekopdracht uitvoeren, zoals hieronder getoond:

• array verwijst naar het bereik waaruit je de waarde wilt ophalen.
• lookup_value verwijst naar de waarde die je wilt zoeken, waarbij rekening wordt gehouden met hoofdletters en kleine letters, in de lookup_array.
• lookup_array verwijst naar het bereik waarin MATCH moet vergelijken met lookup_value.

Stel, je wilt de examenuitslag van JIMMY weten. Gebruik dan de volgende formule:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Opmerking: Dit is een matrixformule die je moet invoeren met Ctrl + Shift + Enter, behalve in Excel365, Excel2021 en nieuwere versies.

INDEX en MATCH voor het vinden van de dichtstbijzijnde overeenkomst

In Excel kun je situaties tegenkomen waarin je de dichtstbijzijnde overeenkomst met een bepaalde waarde in een dataset wilt vinden. In zulke gevallen is het combineren van INDEX en MATCH met de functies ABS en MIN bijzonder handig.

• array verwijst naar het bereik waaruit je de waarde wilt ophalen.
• lookup_array verwijst naar het bereik met waarden waarin je de dichtstbijzijnde overeenkomst met lookup_value wilt vinden.
• lookup_value verwijst naar de waarde waarvan je de dichtstbijzijnde overeenkomst wilt vinden.

Stel, je wilt weten wiens score het dichtst bij85 ligt. Gebruik dan de volgende formule om de dichtstbijzijnde score bij85 te zoeken in C2:C11 en de bijbehorende waarde uit A2:A11 op te halen.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Opmerking: Dit is een matrixformule die je moet invoeren met Ctrl + Shift + Enter, behalve in Excel365, Excel2021 en nieuwere versies.

INDEX en MATCH voor zoeken op meerdere criteria

Wil je een waarde vinden die aan meerdere voorwaarden voldoet en moet je zoeken over twee of meer kolommen, gebruik dan de volgende formule. Hiermee kun je een meervoudige voorwaarden zoeken uitvoeren door verschillende criteria op te geven over meerdere kolommen, zodat je de gewenste waarde vindt die aan alle opgegeven voorwaarden voldoet.

√ Opmerking: Dit is een matrixformule die je moet invoeren met Ctrl + Shift + Enter. Er verschijnen dan accolades in de formulebalk.

• array verwijst naar het bereik waaruit je de waarde wilt ophalen.
• (lookup_value=lookup_array) staat voor één voorwaarde. Deze controleert of een bepaalde lookup_value overeenkomt met de waarden in de lookup_array.

Stel, je wilt de score van Coco uit klas A vinden, met geboortedatum7/2/2008. Gebruik dan de volgende formule:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX en MATCH voor zoeken over meerdere kolommen

Stel je werkt met meerdere gegevenskolommen. De eerste kolom fungeert als sleutel om de gegevens in de andere kolommen te classificeren. Om de categorie of classificatie voor een specifieke invoer te bepalen, moet je zoeken over de gegevenskolommen en deze koppelen aan de relevante sleutel in de referentiekolom.

Hoe kun je in de onderstaande tabel de student Shawn koppelen aan zijn klas met INDEX en MATCH? Je kunt dit bereiken met een formule, maar die is vrij uitgebreid en lastig te begrijpen, laat staan te onthouden en in te voeren.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Daarom is de functie Index en overeenkomst meerdere kolommen van Kutools voor Excel zo handig. Hiermee wordt het proces vereenvoudigd en kun je snel en eenvoudig specifieke invoeren koppelen aan hun categorieën. Om deze krachtige tool te gebruiken en Shawn moeiteloos aan zijn klas te koppelen, download en installeer je eenvoudig de Kutools voor Excel-invoegtoepassing en volg je deze stappen:

1. Selecteer de doelcel waarin je de bijbehorende klas wilt weergeven.
2. Klik op het tabblad Kutools, vervolgens op Formulehulp > Zoeken & Referentie > Index en overeenkomst meerdere kolommen.
3. 
3. Voer in het pop-upvenster het volgende uit:
   1. Klik op de1e knop naast Zoekkolom om de kolom te selecteren met de sleutelgegevens die je wilt retourneren, bijvoorbeeld de klassenamen. (Hier kun je slechts één kolom selecteren.)
   2. Klik op de2e knop naast Tabelbereik om de cellen te selecteren waarmee je de waarden in de geselecteerde Zoekkolom, oftewel de studentnamen, wilt vergelijken.
   3. Klik op de3e knop naast Zoekwaarde om de cel te selecteren met de naam van de student die je wilt koppelen aan zijn klas, in dit geval Shawn.
   4. Klik op OK.
   5. 

Resultaat

Kutools heeft de formule automatisch gegenereerd en je ziet direct de klasnaam van Shawn in de doelcel verschijnen.

INDEX en MATCH voor het zoeken naar de eerste niet-lege waarde

Om de eerste niet-lege waarde op te halen, waarbij fouten worden genegeerd, uit een kolom of rij, kun je een formule gebruiken op basis van INDEX en MATCH. Wil je fouten niet negeren, voeg dan de ISBLANK-functie toe.

• Eerste niet-lege waarde in een kolom of rij ophalen (fouten negeren):

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Eerste niet-lege waarde in een kolom of rij ophalen (inclusief fouten):

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX en MATCH voor het zoeken naar de eerste numerieke waarde

Om de eerste numerieke waarde uit een kolom of rij op te halen, gebruik je een formule op basis van INDEX, MATCH en ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX en MATCH voor het zoeken naar MAX- of MIN-koppelingen

Wil je een waarde ophalen die gekoppeld is aan de maximale of minimale waarde binnen een bereik, gebruik dan de functies MAX of MIN samen met INDEX en MATCH.

• INDEX en MATCH om een waarde op te halen die gekoppeld is aan de maximale waarde:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array,0))
• INDEX en MATCH om een waarde op te halen die gekoppeld is aan de minimale waarde:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array,0))
• In bovenstaande formules zijn er twee argumenten:
• • array verwijst naar het bereik waaruit je de gerelateerde informatie wilt ophalen.
  • lookup_array staat voor de reeks waarden die je wilt onderzoeken of doorzoeken op specifieke criteria, zoals maximale of minimale waarden.

Stel, je wilt weten wie de hoogste score heeft. Gebruik dan de volgende formule:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tip: Pas je eigen #N/B-foutmeldingen aan

Bij het werken met de INDEX- en MATCH-functies in Excel kun je de foutmelding #N/B tegenkomen als er geen overeenkomst is. Bijvoorbeeld, in de onderstaande tabel verschijnt een #N/B-fout wanneer je probeert de score van een student genaamd Samantha te vinden, omdat zij niet in de dataset voorkomt.

Om je spreadsheets gebruiksvriendelijker te maken, kun je deze foutmelding aanpassen door je INDEX-MATCH-formule in de IFNA-functie te plaatsen:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Bovenstaand vind je alle relevante informatie over de INDEX- en MATCH-functies in Excel. Hopelijk heb je iets aan deze handleiding. Wil je meer Excel-tips en -trucs ontdekken, klik dan hier voor ons uitgebreide aanbod van duizenden tutorials.