U kunt de Excel MATCH-functie gebruiken om de relatieve positie van een specifieke waarde in een celbereik of een matrix te vinden.
De MATCH-functie is vergelijkbaar met de VERT.ZOEKEN-functie, omdat ze beide zijn gecategoriseerd onder Excel-zoek-/referentiefuncties. VERT.ZOEKEN zoekt naar een specifieke waarde in een kolom en retourneert een waarde in dezelfde rij, terwijl de MATCH-functie naar een bepaalde waarde in een bereik zoekt en de positie van die waarde retourneert.
Excel MATCH-functie zoekt naar een opgegeven waarde in een celbereik of een matrix en retourneert de relatieve positie van de eerste verschijning van die waarde in het bereik. De MATCH-functie kan ook worden gebruikt om een bepaalde waarde op te zoeken en de bijbehorende waarde terug te geven met behulp van de INDEX-functie (net als Vlookup). Laten we eens kijken hoe we de Excel MATCH-functie kunnen gebruiken om de positie van een opzoekwaarde in een celbereik te vinden.
Excel MATCH-functie
De MATCH-functie is een ingebouwde functie in Excel en wordt voornamelijk gebruikt voor het lokaliseren van de relatieve positie van een opzoekwaarde in een kolom of rij.
Syntaxis van MATCH-functie:
=VERGELIJKEN(opzoekwaarde,opzoekmatrix,[overeenkomst_type})Waar:
opzoekwaarde - De waarde die u wilt opzoeken in een opgegeven celbereik of in een matrix. Dit kan een numerieke waarde, tekstwaarde, logische waarde of een celverwijzing met een waarde zijn.
lookup_array – De reeksen cellen waarin u naar een waarde zoekt. Het moet een enkele kolom of een enkele rij zijn.
match_type – Het is een optionele parameter die kan worden ingesteld op 0,1 of -1 en de standaardwaarde is 1.
- 0 zoekt naar een exacte overeenkomst, wanneer deze niet wordt gevonden, retourneert een fout.
- -1 zoekt naar de kleinste waarde die groter is dan of gelijk is aan lookup_value wanneer de opzoekarray in oplopende volgorde wordt weergegeven.
- 1 zoekt naar de grootste waarde die kleiner is dan of gelijk is aan de look_up-waarde wanneer de opzoekarray in aflopende volgorde wordt weergegeven.
Vind positie van een exacte match
Laten we aannemen dat we de volgende dataset hebben waar we de positie van een bepaalde waarde willen vinden.
In deze tabel willen we de positie van een plaatsnaam (Memphis) in kolom (A2:A23) vinden, dus gebruiken we deze formule:
=VERGELIJKEN("memphis",A2:A23,0)Het derde argument is ingesteld op '0' omdat we een exacte overeenkomst met de plaatsnaam willen vinden. Zoals u kunt zien, is de plaatsnaam "memphis" in de formule in kleine letters, terwijl in de tabel de eerste letter van de plaatsnaam in hoofdletters is (Memphis). Toch kan de formule de positie van de opgegeven waarde in het opgegeven bereik vinden. Dit komt omdat de MATCH-functie niet hoofdlettergevoelig is.
Opmerking: Als de lookup_value niet wordt gevonden in het opzoekbereik of als u het verkeerde opzoekbereik opgeeft, retourneert de functie de fout #N/A.
U kunt een celverwijzing gebruiken in het eerste argument van de functie in plaats van een directe waarde. De onderstaande formule vindt de positie van de waarde in cel F2 en retourneert het resultaat in cel F3.
Vind positie van een geschatte match
Er zijn twee manieren waarop u kunt zoeken naar een geschatte of exacte overeenkomst met de opzoekwaarde en de positie ervan kunt retourneren.
- Een manier is om de kleinste waarde te vinden die groter is dan of gelijk is aan (de volgende grootste overeenkomst) met de opgegeven waarde. Dit kan worden bereikt door het laatste argument (match_type) van de functie in te stellen als '-1'
- Een andere manier is de grootste waarde die kleiner is dan of gelijk is aan (de volgende kleinste overeenkomst) met de gegeven waarde. Dit kan worden bereikt door het match_type van de functie in te stellen op '1'
Volgende kleinste overeenkomst
Als de functie geen exacte overeenkomst met de opgegeven waarde kan vinden wanneer het overeenkomsttype is ingesteld op '1', lokaliseert het de grootste waarde die iets kleiner is dan de opgegeven waarde (wat de op één na kleinste waarde betekent) en retourneert de positie . Om dit te laten werken, moet u de array in oplopende volgorde sorteren, anders resulteert dit in een fout.
In het voorbeeld gebruiken we de onderstaande formule om de eerstvolgende kleinste overeenkomst te vinden:
=VERGELIJKEN(F2,D2:D23,1)Wanneer deze formule de exacte overeenkomst voor de waarde in cel F2 niet kon vinden, wijst deze naar de positie (16) van de volgende kleinste waarde, namelijk 98.
Volgende grootste overeenkomst
Wanneer het zoektype is ingesteld op '-1' en de MATCH-functie geen exacte overeenkomst kan vinden, vindt het de kleinste waarde die groter is dan de opgegeven waarde (wat de op één na grootste waarde betekent) en retourneert zijn positie. De opzoekarray moet voor deze methode in aflopende volgorde worden gesorteerd, anders wordt er een fout geretourneerd.
Voer bijvoorbeeld de volgende formule in om de volgende grootste overeenkomst met de opzoekwaarde te vinden:
=VERGELIJKEN(F2,D2:D23,-1)Deze VERGELIJKEN-functie zoekt naar de waarde in F2 (55) in het opzoekbereik D2:D23, en wanneer het de exacte overeenkomst niet kan vinden, retourneert het de positie (16) van de volgende grootste waarde, d.w.z. 58.
Wildcard-overeenkomst
Jokertekens kunnen alleen in de MATCH-functie worden gebruikt als match_type is ingesteld op '0' en de opzoekwaarde een tekenreeks is. Er zijn jokertekens die u kunt gebruiken in de MATCH-functie: een asterisk (*) en een vraagteken (?).
- Vraagteken (?) wordt gebruikt om een enkel teken of letter te matchen met de tekenreeks.
- Sterretje (*) wordt gebruikt om een willekeurig aantal tekens met de tekenreeks te matchen.
We gebruikten bijvoorbeeld twee '?'-jokertekens in de lookup_value (Lo??n) van de MATCH-functie om een waarde te vinden die overeenkomt met de tekenreeks met twee willekeurige tekens (op de plaatsen met jokertekens). En de functie retourneert de relatieve positie van de overeenkomende waarde in cel E5.
=VERGELIJKEN("Lo??n",A2:A22,0)
U kunt het jokerteken (*) op dezelfde manier gebruiken als (?), maar een asterisk wordt gebruikt om een willekeurig aantal tekens te matchen, terwijl een vraagteken wordt gebruikt om een enkel teken te matchen.
Als u bijvoorbeeld 'sp*' gebruikt, kan de functie overeenkomen met speaker, speed of spielberg, enz. Maar als de functie meerdere/dubbele waarden vindt die overeenkomen met de opzoekwaarde, wordt alleen de positie van de eerste waarde geretourneerd.
In het voorbeeld hebben we "Kil*o" ingevoerd in het argument lookup_value. Dus de MATCH()-functie zoekt naar een tekst die 'Kil' aan het begin, 'o' aan het einde en een willekeurig aantal tekens daartussen bevat. 'Kil*o' komt overeen met Kilimanjaro in de array en daarom retourneert de functie de relatieve positie van Kilimanjaro, die 16 is.
INDEX en MATCH
MATCH-functies worden zelden alleen gebruikt. Ze werden vaak gecombineerd met andere functies om krachtige formules te creëren. Wanneer de MATCH-functie wordt gecombineerd met de INDEX-functie, kan deze geavanceerde zoekopdrachten uitvoeren. Veel mensen geven er nog steeds de voorkeur aan om VERT.ZOEKEN te gebruiken om een waarde op te zoeken, omdat het eenvoudiger is, maar INDEX MATCH is flexibeler en sneller dan VERT.ZOEKEN.
VERT.ZOEKEN kan alleen een waarde verticaal opzoeken, d.w.z. kolommen, terwijl INDEX MATCH combo zowel verticale als horizontale opzoekingen kan doen.
INDEX-functie die wordt gebruikt om een waarde op een specifieke locatie in een tabel of een bereik op te halen. De MATCH-functie retourneert de relatieve positie van een waarde in een kolom of rij. Wanneer gecombineerd, vindt de MATCH het rij- of kolomnummer (locatie) van een specifieke waarde en haalt de INDEX-functie een waarde op op basis van dat rij- en kolomnummer.
Syntaxis van de INDEX-functie:
=INDEX(matrix,rij_getal,[col_getal],)Hoe dan ook, laten we eens kijken hoe INDEX MATCH werkt met een voorbeeld.
In onderstaand voorbeeld willen we de ‘Quiz2’ score voor de leerling ‘Anne’ ophalen. Om dat te doen, gebruiken we de onderstaande formule:
=INDEX(B2:F20,MATCH(H2,A2:A20,0),3)INDEX heeft een rij- en kolomnummer nodig om een waarde op te halen. In de bovenstaande formule vindt de geneste MATCH-functie het rijnummer (positie) van de waarde 'Anne' (H2). Dan leveren we dat rijnummer aan de INDEX-functie met een bereik B2:F20 en een kolomnummer (3), dat we specificeren. En de INDEX-functie retourneert de score '91'.
Opzoeken in twee richtingen met INDEX en MATCH
U kunt ook de functies INDEX en VERGELIJKEN gebruiken om een waarde op te zoeken in een tweedimensionaal bereik (tweerichtings opzoeken). In het bovenstaande voorbeeld hebben we de MATCH-functie gebruikt om het rijnummer van een waarde te vinden, maar we hebben het kolomnummer handmatig ingevoerd. Maar we kunnen zowel rij als kolom vinden door twee MATCH-functies te nesten, een in het argument row_num en een andere in het argument column_num van de INDEX-functie.
Gebruik deze formule voor een zoekopdracht in twee richtingen met INDEX en MATCH:
=INDEX(A1:F20,VERGELIJKEN(H2,A2:A20,0),VERGELIJKEN(H3,A1:F1,0))Zoals we weten, kan de MATCH-functie zowel horizontaal als verticaal naar een waarde zoeken. In deze formule vindt de tweede MATCH-functie in het argument kolomnummer de positie van Quiz2 (4) en levert deze aan de INDEX-functie. En de INDEX haalt de score op.
Nu weet u hoe u de Match-functie in Excel moet gebruiken.