Ga naar hoofdinhoud
 

Excel INDEX MATCH: basis- en geavanceerde zoekopdrachten

Auteur: Amanda Li Laatst gewijzigd: 2023-12-29

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 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

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

=INDEX(array, row_num, [column_num])
  • 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)

Excel-indexovereenkomst 01

√ 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)

Excel-indexovereenkomst 02

Dingen die we moeten weten over de INDEX-functie in Excel:
  • De INDEX-functie kan werken met verticale en horizontale bereiken.
  • Als beide rij_nummer en kolom_getal Er worden argumenten gebruikt, rij_nummer gaat voor op de kolom_getalen INDEX haalt de waarde op op het snijpunt van de opgegeven waarde rij_nummer en kolom_getal.

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.


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:

=MATCH(lookup_value, lookup_array, [match_type])
  • 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)

Excel-indexovereenkomst 3

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

Dingen die we moeten weten over de MATCH-functie in Excel:
  • De MATCH-functie retourneert de positie van de opzoekwaarde in de opzoekarray, niet de waarde zelf.
  • De MATCH-functie retourneert de eerste overeenkomst in het geval van duplicaten.
  • Net als de INDEX-functie, kan de MATCH-functie ook werken met verticale en horizontale bereiken.
  • MATCH is niet hoofdlettergevoelig.
  • Indien de opzoekwaarde van de MATCH-formule in de vorm van tekst is, plaatst u deze tussen aanhalingstekens.
  • Indien de opzoekwaarde is niet gevonden in de zoek_array # N / A fout wordt geretourneerd.

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)

Excel-indexovereenkomst 4

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

Excel-indexovereenkomst 5

Het 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)

(AD) Vereenvoudig zoekopdrachten met Kutools: typen van formules is niet vereist!

Kutools for Excel's Super opzoeken biedt een verscheidenheid aan opzoekhulpmiddelen op maat gemaakt om aan al uw wensen te voldoen. Of u nu zoekopdrachten op meerdere criteria uitvoert, in meerdere werkbladen zoekt of één-op-veel zoekopdrachten uitvoert, Super opzoeken vereenvoudigt het proces met slechts een paar klikken. Ontdek deze functies om te zien hoe Super opzoeken verandert de manier waarop u met Excel-gegevens omgaat. Zeg vaarwel tegen het gedoe van het onthouden van complexe formules.

Kutools Opzoekhulpmiddelen

Kutools for Excel - Biedt u meer dan 300 handige functies voor moeiteloze productiviteit. Mis je kans niet om het uit te proberen met een gratis proefperiode van 30 dagen met volledige functionaliteit! Begin nu!


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))

Excel-indexovereenkomst 6

Hoe deze formule werkt:
  • De eerste MATCH-formule vindt de locatie van Evelyn in de lijst A2:A11 en levert 5 als rijnummer voor INDEX.
  • De tweede MATCH-formule bepaalt de kolom voor de scores en rendementen 3 als het kolomnummer voor INDEX.
  • De formule vereenvoudigt tot =INDEX(A2:C11,5,3)en INDEX retourneert 90.

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))

Excel-indexovereenkomst 7

Opmerking: U kunt eenvoudig naar links zoeken naar specifieke waarden met behulp van de ZOEKEN van rechts naar links kenmerk van Kutools for Excel met slechts een paar klikken. Om de functie te implementeren, navigeert u naar de Kutools tabblad in uw Excel en klik op Super opzoeken > ZOEKEN van rechts naar links in de Formule groep.

ZOEKEN van rechts naar links

Als u Kutools niet hebt geïnstalleerd, klik dan hier download en ontvang een gratis proefperiode van 30 dagen met volledige functionaliteit!


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:

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • 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.

Excel-indexovereenkomst 8

Hoe deze formule werkt:
  • De EXACT-functie vergelijkt "JIMMY" met de waarden in de lijst A2: A11, rekening houdend met het hoofdlettergebruik van tekens: als de twee tekenreeksen precies overeenkomen, waarbij rekening wordt gehouden met zowel hoofdletters als kleine letters, retourneert EXACT TRUE; anders keert het terug Juist. Als gevolg hiervan verkrijgen we een array met TRUE- en FALSE-waarden.
  • De MATCH-functie haalt vervolgens de positie van de eerste TRUE-waarde in de array, wat zou moeten zijn 10.
  • Ten slotte haalt INDEX de waarde op bij de 10e positie geleverd door MATCH in de array.

Opmerkingen:

  • Vergeet niet om de formule correct in te voeren door op te drukken Ctrl + Shift + Enter, tenzij je gebruikt Excel 365 or Excel 2021In dat geval drukt u gewoon op Enter.
  • De bovenstaande formule zoekt binnen één enkele lijst C2: C11. Als u wilt zoeken binnen een bereik met meerdere kolommen en rijen, bijvoorbeeld A2: C11, moet u zowel kolom- als rijnummers aanbieden aan INDEX:
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • In deze herziene formule gebruiken we de MATCH-functie om te zoeken naar "JIMMY", waarbij rekening wordt gehouden met tekens in het bereik A2: A11, en zodra we een overeenkomst hebben gevonden, halen we de overeenkomstige waarde op uit de 3e kolom van het bereik A2: C11.

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.

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 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.

Hoe deze formule werkt:
  • ABS(C2:C11-85) berekent het absolute verschil tussen elke waarde in het bereik C2: C11 en 85, resulterend in een array van de absolute verschillen.
  • MIN(ABS(C2:C11-85)) vindt de minimumwaarde in de reeks absolute verschillen, die het dichtst bij 85 ligt.
  • De MATCH-functie MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0) vindt vervolgens de positie van het minimale absolute verschil in de reeks absolute verschillen, wat zou moeten zijn 10.
  • Ten slotte haalt INDEX de waarde op op de positie in de lijst A2: A11 dat overeenkomt met de score die het dichtst bij ligt 85 binnen bereik C2: C11.

Opmerkingen:

  • Vergeet niet om de formule correct in te voeren door op te drukken Ctrl + Shift + Enter, tenzij je gebruikt Excel 365 or Excel 2021In dat geval drukt u gewoon op Enter.
  • In het geval van een gelijkspel retourneert deze formule de eerste wedstrijd.
  • Vinden die het dichtst bij de gemiddelde score ligt, vervangen 85 in de formule met GEMIDDELDE(C2:C11).

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.

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…), 0))

√ 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))

Excel-indexovereenkomst 9

Opmerkingen:

  • In deze formule vermijden we hardcoderingswaarden, waardoor het eenvoudig wordt om een ​​score met verschillende informatie te verkrijgen door de waarden in cellen te wijzigen G2, G3 en G4.
  • U moet de formule invoeren door op te drukken Ctrl + Shift + Enter behalve in Excel 365 or Excel 2021, waar u eenvoudig op kunt drukken Enter.
    Als u voortdurend vergeet te gebruiken Ctrl + Shift + Enter Om de formule te voltooien en onjuiste resultaten te krijgen, gebruikt u de volgende, iets complexere formule, waarmee u een eenvoudige formule kunt voltooien Enter key:
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • De formules kunnen complex zijn en moeilijk te onthouden. Als u het opzoeken van meerdere criteria wilt vereenvoudigen zonder dat u handmatig formules hoeft in te voeren, kunt u overwegen om Kutools for Excel's Opzoeken in meerdere condities functie. Nadat je Kutools hebt geïnstalleerd, navigeer je naar het Kutools tabblad in uw Excel en klik op Super opzoeken > Opzoeken in meerdere condities in de Formule groep.

    Opzoeken in meerdere condities

    Als u Kutools niet hebt geïnstalleerd, klik dan hier download en ontvang een gratis proefperiode van 30 dagen met volledige functionaliteit!


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. Excel-indexovereenkomst 11
  4. Ga in het pop-upvenster als volgt te werk:
    1. Klik op de 1e excel index match icoon 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 excel index match icoon 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 excel index match icoon 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. Excel-indexovereenkomst 12

Resultaat

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

Opmerking: Om de Indexeer en match op meerdere kolommen functie, moet Kutools voor Excel op uw computer zijn geïnstalleerd. Als je het nog niet hebt geïnstalleerd, wacht dan niet --- Download en installeer het nu voor een gratis proefperiode van 30 dagen zonder beperkingen. Laat Excel vandaag nog slimmer werken!


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:B150),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))

Opmerkingen:


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))

Opmerkingen:


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))

Hoe deze formule werkt:
  • MAXIMUM(C2:C11) zoekt naar de hoogste waarde in het bereik C2: C11Dit is 96.
  • De MATCH-functie vindt vervolgens de positie van de hoogste waarde in de array C2: C11, wat zou moeten zijn 1.
  • Ten slotte haalt INDEX het 1st waarde in de lijst A2: A11.

Opmerkingen:

  • In het geval van meer dan één maximum- of minimumwaarde, zoals te zien is in het bovenstaande voorbeeld, waarbij twee leerlingen dezelfde hoogste score behaalden, retourneert deze formule de eerste overeenkomst.
  • Gebruik de volgende formule om te bepalen wie de laagste score heeft:
    =INDEX(A2:A11,MATCH(MIN(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.

Excel-indexovereenkomst 15

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

Excel-indexovereenkomst 16

Opmerkingen:

  • U kunt uw foutmeldingen aanpassen door te vervangen "Niet gevonden" met een tekst naar keuze.
  • Als je alle fouten wilt afhandelen, en niet alleen #N/A, overweeg dan om de IFFOUT functie in plaats van IFNA:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    Houd er rekening mee dat het wellicht niet raadzaam is om alle fouten te onderdrukken, omdat deze dienen als waarschuwing voor mogelijke problemen in uw formules.

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.