De INDEX-functie kan alleen worden gebruikt, maar door de MATCH-functie erin te nesten, ontstaat een geavanceerde zoekopdracht.
Deze geneste functie is flexibeler dan VERT. ZOEKEN en kan sneller resultaten opleveren.
Dit artikel legt uit hoe je de INDEX- en MATCH-functies samen kunt gebruiken in alle versies van Excel, inclusief Excel 2019 en Microsoft 365.
Wat zijn de INDEX- en MATCH-functies?
INDEX en MATCH zijn Excel-zoekfuncties. Hoewel het twee volledig afzonderlijke functies zijn die afzonderlijk kunnen worden gebruikt, kunnen ze ook worden gecombineerd om geavanceerde formules te maken.
De INDEX-functie retourneert een waarde of de verwijzing naar een waarde uit een bepaalde selectie. Het kan bijvoorbeeld worden gebruikt om de waarde in de tweede rij van een gegevensset te vinden, of in de vijfde rij en derde kolom.
Hoewel INDEX heel goed alleen kan worden gebruikt, maakt het nesten van MATCH in de formule het een beetje nuttiger. De MATCH-functie zoekt naar een opgegeven item in een celbereik en retourneert vervolgens de relatieve positie van het item in het bereik. Het kan bijvoorbeeld worden gebruikt om te bepalen dat een specifieke naam het derde item is in een lijst met namen.
Image
INDEX en MATCH-syntaxis en argumenten
Zo moeten beide functies worden geschreven zodat Excel ze kan begrijpen:
=INDEX(array, row_num, [column_num])
array is het cellenbereik dat de formule zal gebruiken. Dit kunnen een of meer rijen en kolommen zijn, zoals A1:D5. Het is verplicht.
row_num is de rij in de array waaruit een waarde moet worden geretourneerd, zoals 2 of 18. Het is vereist tenzij column_num aanwezig is.
column_num is de kolom in de array waaruit een waarde moet worden geretourneerd, zoals 1 of 9. Het is optioneel.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value is de waarde die u wilt matchen in lookup_array. Het kan een getal, tekst of logische waarde zijn die handmatig wordt getypt of waarnaar wordt verwezen via een celverwijzing. Dit is vereist.
lookup_array is het bereik van cellen om doorheen te kijken. Het kan een enkele rij of een enkele kolom zijn, zoals A2:D2 of G1:G45. Dit is vereist.
match_type kan -1, 0 of 1 zijn. Het specificeert hoe lookup_value wordt vergeleken met waarden in lookup_array (zie hieronder). 1 is de standaardwaarde als dit argument wordt weggelaten.
Welk zoektype te gebruiken
Overeenkomsttype
Wat het doet
Regel
Voorbeeld
1
Vindt de grootste waarde die kleiner is dan of gelijk is aan lookup_value.
De lookup_array-waarden moeten in oplopende volgorde worden geplaatst (bijv. -2, -1, 0, 1, 2; of A-Z;, of FALSE, TRUE.
lookup_value is 25 maar het ontbreekt in lookup_array, dus de positie van het volgende kleinste getal, zoals 22, wordt in plaats daarvan geretourneerd.
0
Vindt de eerste waarde die exact gelijk is aan lookup_value.
De lookup_array-waarden kunnen in elke volgorde staan.
lookup_value is 25, dus het retourneert de positie van 25.
-1
Vindt de kleinste waarde die groter of gelijk is aan lookup_value.
De lookup_array-waarden moeten in aflopende volgorde worden geplaatst (bijv. 2, 1, 0, -1, -2).
lookup_value is 25 maar het ontbreekt in lookup_array, dus de positie van het volgende grootste getal, zoals 34, wordt in plaats daarvan geretourneerd.
Gebruik 1 of -1 voor momenten waarop u een benadering bij benadering langs een schaal moet uitvoeren, zoals bij het omgaan met getallen en wanneer benaderingen in orde zijn. Maar onthoud dat als u match_type niet opgeeft, 1 de standaardwaarde is, wat de resultaten kan vertekenen als u echt een exacte overeenkomst wilt.
Voorbeeld INDEX- en MATCH-formules
Voordat we kijken hoe we INDEX en MATCH kunnen combineren in één formule, moeten we begrijpen hoe deze functies op zichzelf werken.
INDEX Voorbeelden
=INDEX(A1:B2, 2, 2)
=INDEX(A1:B1, 1)
=INDEX(2:2, 1)=INDEX(B1:B2, 1)
Image
In dit eerste voorbeeld zijn er vier INDEX-formules die we kunnen gebruiken om verschillende waarden te krijgen:
=INDEX(A1:B2, 2, 2) kijkt door A1:B2 om de waarde in de tweede kolom en tweede rij te vinden, namelijk Stacy.
=INDEX(A1:B1, 1) kijkt door A1:B1 om de waarde in de eerste kolom te vinden, namelijk Jon.
=INDEX(2:2, 1) bekijkt alles in de tweede rij om de waarde in de eerste kolom te vinden, namelijk Tim.
=INDEX(B1:B2, 1) kijkt door B1:B2 om de waarde in de eerste rij te vinden, namelijk Amy.
MATCH Voorbeelden
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=VERGELIJKEN(13, A1:D1, 0)
Image
Hier zijn vier eenvoudige voorbeelden van de MATCH-functie:
=MATCH("Stacy", A2:D2, 0) zoekt naar Stacy in het bereik A2:D2 en geeft 3 als resultaat.
=MATCH(14, D1:D2) zoekt naar 14 in het bereik D1:D2, maar aangezien het niet in de tabel wordt gevonden, vindt MATCH de volgende grootste waarde dat is kleiner dan of gelijk aan 14, wat in dit geval 13 is, wat op positie 1 staat van lookup_array.
=MATCH(14, D1:D2, -1) is identiek aan de formule erboven, maar aangezien de array niet in aflopende volgorde staat zoals -1 vereist, we krijgen een foutmelding.
=MATCH(13, A1:D1, 0) zoekt 13 in de eerste rij van het blad, wat 4 retourneert omdat dit het vierde item in deze array is.
INDEX-MATCH Voorbeelden
Hier zijn twee voorbeelden waar we INDEX en MATCH kunnen combineren in één formule:
Vind celverwijzing in tabel
=INDEX(B2:B5, MATCH(F1, A2:A5))
Image
Dit voorbeeld nestelt de MATCH-formule in de INDEX-formule. Het doel is om de artikelkleur te identificeren aan de hand van het artikelnummer.
Als je naar de afbeelding kijkt, kun je in de "Gescheiden" rijen zien hoe de formules op zichzelf zouden worden geschreven, maar aangezien we ze aan het nesten zijn, is dit wat er gebeurt:
MATCH(F1, A2:A5) zoekt naar de F1-waarde (8795) in de dataset A2:A5. Als we de kolom aftellen, kunnen we zien dat het 2 is, dus dat is wat de MATCH-functie zojuist heeft bedacht.
De INDEX-array is B2:B5 omdat we uiteindelijk op zoek zijn naar de waarde in die kolom.
De INDEX-functie kan nu als volgt worden herschreven aangezien 2 is wat MATCH heeft gevonden: INDEX(B2:B5, 2, [column_num]).
Aangezien column_num optioneel is, kunnen we dat verwijderen om dit te laten staan: INDEX(B2:B5, 2).
Dus nu, dit is als een normale INDEX-formule waarbij we de waarde vinden van het tweede item in B2:B5, dat rood is.
In dit voorbeeld van MATCH en INDEX doen we een zoekopdracht in twee richtingen. Het idee is om te zien hoeveel geld we in mei hebben verdiend met groene artikelen. Dit lijkt erg op het bovenstaande voorbeeld, maar er is een extra MATCH-formule genest in INDEX.
MATCH(G1, A2:A13, 0) is het eerste item dat in deze formule is opgelost. Het zoekt naar G1 (het woord "mei") in A2:A13 om een bepaalde waarde te krijgen. We zien het hier niet, maar het is 5.
MATCH(G2, B1:E1, 0) is de tweede MATCH-formule en lijkt erg op de eerste, maar zoekt in plaats daarvan naar G2 (het woord "Groen") in de kolomkoppen bij B1:E1. Deze lost op tot 3.
We kunnen de INDEX-formule nu als volgt herschrijven om te visualiseren wat er gebeurt: =INDEX(B2:E13, 5, 3). Dit zoekt in de hele tabel, B2:E13, naar de vijfde rij en derde kolom, wat $ 180 oplevert.
MATCH- en INDEX-regels
Er zijn verschillende dingen om in gedachten te houden bij het schrijven van formules met deze functies:
MATCH is niet hoofdlettergevoelig, dus hoofdletters en kleine letters worden hetzelfde behandeld bij het matchen van tekstwaarden.
MATCH retourneert N/A om meerdere redenen: als match_type 0 is en lookup_value niet wordt gevonden als match_type -1 is en lookup_array niet in aflopende volgorde staat, als match_type 1 is en lookup_array niet in oplopend is volgorde, en als lookup_array geen enkele rij of kolom is.
U kunt een jokerteken gebruiken in het lookup_value-argument als match_type 0 is en lookup_value een tekenreeks is. Een vraagteken komt overeen met elk afzonderlijk teken en een asterisk komt overeen met elke reeks tekens (bijv.g., =MATCH("Jo", 1:1, 0)). Als u MATCH wilt gebruiken om een echt vraagteken of sterretje te vinden, typt u eerst ~.
INDEX geeft REF! als row_num en column_num niet naar een cel in de array verwijzen.
Verwante Excel-functies
De MATCH-functie is vergelijkbaar met LOOKUP, maar MATCH retourneert de positie van het item in plaats van het item zelf.
VERT. ZOEKEN is een andere opzoekfunctie die u in Excel kunt gebruiken, maar in tegenstelling tot VERGELIJKEN waarvoor INDEX vereist is voor geavanceerde opzoekacties, hebben VERT. ZOEKEN-formules slechts die ene functie nodig.
Als je een lijst met datums hebt, gebruik dan de MAAND-formule in Excel om het serienummer voor de maand te extraheren en verander dat nummer vervolgens in de naam van de maand
Hoe Booleaanse waarden worden gebruikt in Excel en Google Spreadsheets, en hoe ze kunnen worden omgezet in numerieke waarden voor gebruik in formules. Bijgewerkt om Excel 2019 op te nemen
ITunes Match maakt het eenvoudig om je muziek op verschillende apparaten gesynchroniseerd te houden, maar niet elk aspect van het gebruik ervan is eenvoudig of duidelijk
