In Excel is het nauwkeurig ophalen van specifieke gegevens vaak een noodzaak. Hoewel de INDEX- en MATCH-functies elk hun eigen sterke punten hebben, ontgrendelt de combinatie ervan een krachtige toolset voor het opzoeken van gegevens. Samen faciliteren ze een reeks zoekmogelijkheden, van eenvoudige horizontale en verticale zoekopdrachten tot meer geavanceerde functionaliteiten zoals tweerichtings-, hoofdlettergevoelige en multicriteria-zoekopdrachten. De combinatie van INDEX en MATCH biedt verbeterde mogelijkheden vergeleken met VLOOKUP en maakt een breder scala aan opties voor het opzoeken van gegevens mogelijk. Laten we in deze tutorial dieper ingaan op de mogelijkheden die ze samen kunnen bereiken.

• INDEX-functie
• MATCH-functie
• Combineer INDEX en MATCH

• Opzoeken in twee richtingen
• Links opzoeken
• Hoofdlettergevoelig opzoeken
• Dichtstbijzijnde wedstrijd
• Opzoeken met meerdere criteria
• Zoek in meerdere kolommen
• Zoek naar de eerste niet-lege waarde
• Zoek naar de eerste numerieke waarde
• Zoek naar MAX- of MIN-associaties
• Tip: Pas uw eigen #N/B-foutmeldingen aan

INDEX en MATCH gebruiken in Excel

Voordat we de functies INDEX en MATCH gebruiken, moeten we eerst weten hoe INDEX en MATCH ons kunnen helpen bij het opzoeken van waarden.

Hoe de INDEX-functie in Excel te gebruiken

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

• reeks (vereist) verwijst naar het bereik waaruit u de waarde wilt retourneren.
• rij_nummer (vereist, tenzij kolom_getal aanwezig is) verwijst naar het rijnummer van de array.
• kolom_getal (optioneel, maar verplicht indien rij_nummer wordt weggelaten) verwijst naar het kolomnummer van de array.

Om bijvoorbeeld te weten de score van Jef 6e leerling op de lijst, kunt u de INDEX-functie als volgt gebruiken:

=INDEX(C2:C11,6)

√ Opmerking: het bereik C2: C11 is waar de scores worden vermeld, terwijl het nummer 6 vindt de examenscore van de 6e leerling.

Laten we hier een kleine test doen. Voor de formule: =INDEX(A1:C1,2), welke waarde zal het opleveren? --- Ja, het komt terug Geboortedatum 2nd-waarde in de gegeven rij.

Nu moeten we weten dat de INDEX-functie perfect kan werken met horizontale of verticale bereiken. Maar wat als we het nodig hebben om een ​​waarde in een groter bereik met meerdere rijen en kolommen te retourneren? Welnu, in dit geval moeten we zowel een rijnummer als een kolomnummer toepassen. Om er bijvoorbeeld achter te komen Jeffs score binnen het tabelbereik in plaats van een enkele kolom, kunnen we zijn score lokaliseren met a rijnummer 6 en kolomnummer 3 in de cellen via A2 tot C11 soortgelijk:

=INDEX(A2:C11,6,3)

Voor een echt grote database met meerdere rijen en kolommen is het voor ons zeker niet handig om de formule met een exact rijnummer en kolomnummer toe te passen. En dit is het wanneer we het gebruik van de MATCH-functie moeten combineren.

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Edge, Firefox en Safari!
Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

Hoe de MATCH-functie in Excel te gebruiken

De MATCH-functie in Excel retourneert een numerieke waarde, de locatie van een specifiek item in het opgegeven bereik. De syntaxis van de MATCH-functie is als volgt:

• opzoekwaarde (vereist) verwijst naar de waarde die moet overeenkomen in de zoek_array.
• zoek_array (vereist) verwijst naar het cellenbereik waarin u wilt dat MATCH zoekt.
• match_type (Optioneel): 1, 0 or -1.
  • 1 (standaard), MATCH vindt de grootste waarde die kleiner is dan of gelijk is aan de opzoekwaarde. De waarden in de zoek_array moet in oplopende volgorde worden geplaatst.
  • 0, MATCH vindt de eerste waarde die exact gelijk is aan de opzoekwaarde. De waarden in de zoek_array kan in willekeurige volgorde. (Voor de gevallen waarin het zoektype is ingesteld op 0, kunt u jokertekens gebruiken.)
  • -1, MATCH vindt de kleinste waarde die groter is dan of gelijk is aan de opzoekwaarde. De waarden in de zoek_array moet in aflopende volgorde worden geplaatst.

Om bijvoorbeeld te weten de positie van Vera in de namenlijst, kunt u de MATCH-formule als volgt gebruiken:

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

√ Let op: Het resultaat “4” geeft aan dat de naam “Vera” op de 4e positie van de lijst staat.

Nu we het basisgebruik van de INDEX- en MATCH-functies in Excel kennen, stropen we de mouwen op en maken we ons klaar om de twee functies te combineren.

Hoe INDEX en MATCH te combineren in Excel

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

Vinden Evelyns score, met de wetenschap dat de examenscores in de 3derde kolom, dat kunnen we gebruik de MATCH-functie om automatisch de rijpositie te bepalen zonder dat u het handmatig hoeft te tellen. Vervolgens kunnen we de INDEX-functie gebruiken om het waarde op het snijpunt van de geïdentificeerde rij en de derde kolom:

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

Omdat de formule er misschien wat ingewikkeld uitziet, laten we elk onderdeel ervan doornemen.

De INDEX formule bevat drie argumenten:

• rij_nummer: VERGELIJKEN("Evelyn",A2:A11,0) voorziet INDEX van de rijpositie van de waarde "Evelyn" binnen bereik A2: A11Dit is 5.
• kolom_getal: 3 specificeert de 3derde kolom voor INDEX om de score binnen de array te lokaliseren.
• reeks: A2: C11 instrueert INDEX om de overeenkomende waarde te retourneren op het snijpunt van de opgegeven rij en kolom, binnen het bereik van A2 tot C11. Eindelijk krijgen we het resultaat 90.

In de bovenstaande formule hebben we een hardgecodeerde waarde gebruikt, "Evelyn". In de praktijk zijn hardgecodeerde waarden echter onpraktisch, omdat ze elke keer dat we naar andere gegevens zoeken, moeten worden aangepast, zoals de score van een andere leerling. In dergelijke scenario's kunnen we celverwijzingen gebruiken om dynamische formules te maken. In dit geval zal ik dat bijvoorbeeld doen verander "Evelyn" in F2:

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

INDEX- en MATCH-formulevoorbeelden

In dit deel zullen we het hebben over verschillende omstandigheden om de INDEX- en MATCH-functies te gebruiken om aan verschillende behoeften te voldoen.

INDEX en MATCH om een ​​zoekopdracht in twee richtingen toe te passen

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

In dergelijke gevallen kunnen we een tweerichtingszoekopdracht uitvoeren, ook wel een matrixzoekopdracht genoemd, door twee MATCH-functies te gebruiken: de ene om het rijnummer te vinden en de andere om het kolomnummer te bepalen. Om te weten bijvoorbeeld Evelyns score, moeten we de formule gebruiken:

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

INDEX en MATCH om een ​​zoekopdracht naar links toe te passen

Laten we nu een scenario bekijken waarin u de klasse van Evelyn moet bepalen. Het is je misschien opgevallen dat de klassenkolom links van de naamkolom is geplaatst, een situatie die de mogelijkheden van een andere krachtige Excel-opzoekfunctie, VLOOKUP, te boven gaat.

In feite is de mogelijkheid om zoekopdrachten aan de linkerkant uit te voeren een van de aspecten waarbij de combinatie van INDEX en MATCH VLOOKUP overtreft.

Vinden Evelyns klas, gebruik de volgende formule om zoek naar Evelyn in B2:B11 en haal de corresponderende waarde op uit A2:A11.

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

INDEX en MATCH om een ​​hoofdlettergevoelige zoekopdracht toe te passen

De MATCH-functies zijn inherent hoofdletterongevoelig. Maar als u in uw formule onderscheid wilt maken tussen hoofdletters en kleine letters, kunt u deze verbeteren door de EXACT functie. Door de MATCH-functie te combineren met EXACT in een INDEX-formule, kunt u vervolgens effectief hoofdlettergevoelig opzoeken, zoals hieronder weergegeven:

• reeks verwijst naar het bereik waaruit u de waarde wilt retourneren.
• opzoekwaarde verwijst naar de waarde die moet overeenkomen, rekening houdend met het geval van tekens, in de zoek_array.
• zoek_array verwijst naar het cellenbereik waarmee u MATCH wilt vergelijken opzoekwaarde.

Om bijvoorbeeld te weten JIMMY's examenscore, gebruik de volgende formule:

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

√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter, behalve in Excel 365 en Excel 2021.

INDEX en MATCH om de beste match te vinden

In Excel kunt u situaties tegenkomen waarin u de dichtstbijzijnde of beste overeenkomst met een specifieke waarde binnen een gegevensset moet vinden. In dergelijke scenario's kan het gebruik van een combinatie van INDEX- en MATCH-functies, samen met de ABS- en MIN-functies, ongelooflijk nuttig zijn.

• reeks verwijst naar het bereik waaruit u de waarde wilt retourneren.
• zoek_array verwijst naar het bereik van waarden waar u de beste overeenkomst mee wilt vinden opzoekwaarde.
• opzoekwaarde verwijst naar de waarde om de beste overeenkomst te vinden.

Om er bijvoorbeeld achter te komen wiens score het dichtst bij 85 ligt, gebruik dan de volgende formule zoek naar de score die het dichtst bij 85 ligt in C2:C11 en haal de corresponderende waarde op uit A2:A11.

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

√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter, behalve in Excel 365 en Excel 2021.

Verbazingwekkend! Efficiënte tabbladen gebruiken in Excel zoals Chrome, Edge, Firefox en Safari!
Bespaar 50% van uw tijd en verminder elke dag duizenden muisklikken!

INDEX en MATCH om een ​​zoekopdracht met meerdere criteria toe te passen

Om een ​​waarde te vinden die aan meerdere voorwaarden voldoet, waarbij u in twee of meer kolommen moet zoeken, gebruikt u de volgende formule. Met de formule kunt u op meerdere criteria zoeken door verschillende voorwaarden in verschillende kolommen op te geven, zodat u de gewenste waarde kunt vinden die aan alle opgegeven criteria voldoet.

√ Opmerking: dit is een matrixformule waarvoor u moet invoeren met Ctrl + Shift + Enter. Er verschijnt dan een paar accolades in de formulebalk.

• reeks verwijst naar het bereik waaruit u de waarde wilt retourneren.
• (lookup_value=lookup_array) vertegenwoordigt één enkele voorwaarde. Deze voorwaarde controleert of een bepaald opzoekwaarde komt overeen met de waarden in de zoek_array.

Om bijvoorbeeld de te vinden score van Klasse A's Coco, wiens geboortedatum 7-2-2008 is, kunt u de volgende formule gebruiken:

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

INDEX en MATCH om een ​​zoekopdracht toe te passen op meerdere kolommen

Stel u een scenario voor waarin u te maken heeft 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 specifiek item te bepalen, moet u een zoekopdracht uitvoeren in de gegevenskolommen en deze koppelen aan de relevante sleutel in de referentiekolom.

Hoe kunnen we in de onderstaande tabel bijvoorbeeld de leerling Shawn matchen met zijn overeenkomstige klas met behulp van INDEX en MATCH? Nou ja, je kunt dit bereiken met een formule, maar de formule is behoorlijk uitgebreid en kan een uitdaging zijn om te begrijpen, laat staan ​​te onthouden en te typen.

=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)), "")

Dat is waar Kutools for Excel's Indexeer en match op meerdere kolommen functie komt van pas. Het vereenvoudigt het proces, waardoor het snel en eenvoudig wordt om specifieke inzendingen te matchen met de bijbehorende categorieën. Om dit krachtige hulpmiddel te ontgrendelen en Shawn moeiteloos aan zijn klas te koppelen, is het eenvoudig download en installeer de Kutools voor Excel-invoegtoepassingen doe het dan als volgt:

1. Selecteer de doelcel waarin u de overeenkomende klasse wilt weergeven.
2. Op de Kutools tab, klik Formule Helper > Opzoeken en verwijzen > Indexeer en match op meerdere kolommen.
3. 
3. Ga in het pop-upvenster als volgt te werk:
   1. Klik op de 1e knop naast Opzoeken_kol om de kolom te selecteren die de sleutelinformatie bevat die u wilt retourneren, bijvoorbeeld de klassennamen. (U kunt hier slechts één kolom selecteren.)
   2. Klik op de 2e knop naast Tabel_rng om de cellen te selecteren die overeenkomen met de waarden in de geselecteerde Opzoeken_kol, dwz de namen van de studenten.
   3. Klik op de 3e knop naast Opzoekwaarde om de cel te selecteren met de naam van de leerling die je wilt matchen met de klas, in dit geval Shawn.
   4. Klik OK.
   5. 

Resultaat

Kutools heeft de formule automatisch gegenereerd en de klassenaam van Shawn wordt onmiddellijk in de doelcel weergegeven.

INDEX en MATCH om te 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, kunt u een formule gebruiken die is gebaseerd op de functies INDEX en MATCH. Als u de fouten uit uw bereik echter niet wilt negeren, voegt u de ISBLANK-functie toe.

• Haal de eerste niet-lege waarde in een kolom of rij op, waarbij fouten worden genegeerd:

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

• Haal de eerste niet-lege waarde op in een kolom of rij inclusief fouten:

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

  

INDEX en MATCH om de eerste numerieke waarde op te zoeken

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

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

INDEX en MATCH om MAX- of MIN-associaties op te zoeken

Als u een waarde wilt ophalen die is gekoppeld aan de maximum- of minimumwaarde binnen een bereik, kunt u de MAX- of MIN-functie gebruiken in combinatie met de INDEX- en MATCH-functies.

• INDEX en MATCH om een ​​waarde op te halen die is gekoppeld aan de maximale waarde:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX en MATCH om een ​​waarde op te halen die is gekoppeld aan de minimumwaarde:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Er zijn twee argumenten in de bovenstaande formules:
• • reeks verwijst naar het bereik waaruit u de gerelateerde informatie wilt retourneren.
  • zoek_array vertegenwoordigt de reeks waarden die moeten worden onderzocht of gezocht op specifieke criteria, dat wil zeggen maximale of minimale waarden.

Als u bijvoorbeeld wilt bepalen wie de hoogste score heeft, gebruik de volgende formule:

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

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

Wanneer u met de INDEX- en MATCH-functies van Excel werkt, kunt u de fout #N/A tegenkomen als er geen overeenkomend resultaat is. In de onderstaande tabel verschijnt bijvoorbeeld de fout #N/B wanneer u probeert de score van een leerling met de naam Samantha te vinden, omdat zij niet aanwezig is in de dataset.

Om uw spreadsheets gebruiksvriendelijker te maken, kunt u dit foutbericht aanpassen door uw INDEX MATCH-formule in de IFNA-functie te plaatsen:

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

Hierboven vindt u alle relevante inhoud met betrekking tot de INDEX- en MATCH-functies in Excel. Ik hoop dat je de tutorial nuttig vindt. Als u meer tips en trucs voor Excel wilt ontdekken, klik dan hier om toegang te krijgen tot onze uitgebreide collectie van meer dan duizenden tutorials.