Du kan använda Excel-MATCH-funktionen för att hitta den relativa positionen för ett specifikt värde i ett cellområde eller en matris.
MATCH-funktionen liknar VLOOKUP-funktionen eftersom de båda kategoriseras under Excel Lookup/Reference Functions. VLOOKUP söker efter ett specifikt värde i en kolumn och returnerar ett värde i samma rad medan MATCH-funktionen söker efter ett visst värde i ett område och returnerar positionen för det värdet.
Excel MATCH-funktionen letar efter ett specificerat värde i ett cellintervall eller en matris och returnerar den relativa positionen för det första uppträdandet av det värdet i intervallet. MATCH-funktionen kan också användas för att slå upp ett visst värde och returnera dess motsvarande värde med hjälp av INDEX-funktionen (precis som Vlookup). Låt oss se hur du använder Excel MATCH-funktionen för att hitta positionen för ett uppslagsvärde i ett cellintervall.
Excel MATCH-funktion
MATCH-funktionen är en inbyggd funktion i Excel och den används främst för att lokalisera den relativa positionen för ett uppslagsvärde i en kolumn eller en rad.
Syntax för MATCH-funktionen:
=MATCH(uppslagsvärde,uppslagsmatris,[matchningstyp})Var:
lookup_value – Värdet du vill slå upp i ett specificerat cellområde eller i en array. Det kan vara ett numeriskt värde, textvärde, logiskt värde eller en cellreferens som har ett värde.
lookup_array – De arrayer av celler där du söker efter ett värde. Det måste vara en enda kolumn eller en enda rad.
matchningstyp – Det är en valfri parameter som kan ställas in på 0,1 eller -1 och standardvärdet är 1.
- 0 letar efter en exakt matchning, när den inte hittas returneras ett fel.
- -1 letar efter det minsta värdet som är större än eller lika med lookup_value när uppslagsmatrisen i stigande ordning.
- 1 letar efter det största värdet som är mindre än eller lika med look_up-värdet när uppslagsmatrisen i fallande ordning.
Hitta position för en exakt matchning
Låt oss anta att vi har följande datauppsättning där vi vill hitta positionen för ett visst värde.
I den här tabellen vill vi hitta positionen som ett stadsnamn (Memphis) i kolumn (A2:A23), så vi använder denna formel:
=MATCH("memphis",A2:A23,0)Det tredje argumentet är satt till '0' eftersom vi vill hitta en exakt matchning av stadsnamnet. Som du kan se är stadsnamnet "memphis" i formeln med gemener medan den första bokstaven i stadsnamnet i tabellen står med versaler (Memphis). Fortfarande kan formeln hitta positionen för det angivna värdet i det givna intervallet. Det beror på att MATCH-funktionen är skiftlägesokänslig.
Notera: Om lookup_value inte hittas i uppslagsintervallet eller om du anger fel uppslagsintervall, kommer funktionen att returnera #N/A-felet.
Du kan använda en cellreferens i funktionens första argument istället för ett direktvärde. Formeln nedan hittar värdets position i cell F2 och returnerar resultatet i cell F3.
Hitta position för en ungefärlig matchning
Det finns två sätt du kan leta efter en ungefärlig eller exakt matchning av uppslagsvärdet och returnera dess position.
- Ett sätt är att hitta det minsta värdet som är större än eller lika med (näst största matchning) med det angivna värdet. Det kan uppnås genom att sätta det sista argumentet (match_type) för funktionen som "-1"
- Ett annat sätt är det största värdet som är mindre än eller lika (näst minsta matchning) med det givna värdet. Det kan uppnås genom att ställa in matchningstyp för funktionen som '1'
Nästa minsta match
Om funktionen inte kan hitta en exakt matchning till det angivna värdet när matchningstypen är inställd på '1', lokaliserar den det största värdet som är något mindre än det angivna värdet (vilket betyder det näst minsta värdet) och returnerar dess position . För att detta ska fungera måste du sortera arrayen i stigande ordning, annars kommer det att resultera i ett fel.
I exemplet använder vi formeln nedan för att hitta nästa minsta matchning:
=MATCH(F2;D2:D23;1)När denna formel inte kunde hitta den exakta matchningen för värdet i cell F2, pekar den på positionen (16) för det näst minsta värdet, dvs. 98.
Nästa största match
När matchningstypen är inställd på '-1' och MATCH-funktionen inte kan hitta en exakt matchning, hittar den det minsta värdet som är större än det angivna värdet (vilket betyder det näst största värdet) och returnerar sin position. Uppslagsmatrisen måste sorteras i fallande ordning för den här metoden, annars returnerar den ett fel.
Ange till exempel följande formel för att hitta den näst största matchningen till uppslagsvärdet:
=MATCH(F2;D2:D23;-1)Denna MATCH-funktion söker efter värdet i F2 (55) i uppslagsintervallet D2:D23, och när den inte kan hitta den exakta matchningen returnerar den positionen (16) för det näst största värdet, dvs. 58.
Wildcard Match
Jokertecken kan endast användas i MATCH-funktionen när match_type är inställd på '0' och uppslagsvärdet är en textsträng. Det finns jokertecken du kan använda i MATCH-funktionen: en asterisk (*) och ett frågetecken (?).
- Frågetecken (?) används för att matcha varje enskilt tecken eller bokstav med textsträngen.
- Asterisk (*) används för att matcha valfritt antal tecken med strängen.
Till exempel använde vi två jokertecken "?" i lookup_value (Lo??n) i MATCH-funktionen för att hitta ett värde som matchar textsträngen med två valfria tecken (på jokerteckenplatserna). Och funktionen returnerar den relativa positionen för det matchande värdet i cell E5.
=MATCH("Lo??n",A2:A22,0)
Du kan använda jokertecken (*) på samma sätt som (?), men en asterisk används för att matcha valfritt antal tecken medan ett frågetecken används för att matcha ett enskilt tecken.
Till exempel, om du använder 'sp*', kan funktionen matcha med högtalare, hastighet eller spielberg, etc. Men om funktionen hittar flera/dubbletter av värden som matchar uppslagsvärdet, kommer den bara att returnera positionen för det första värdet.
I exemplet skrev vi in "Kil*o" i argumentet lookup_value. Så MATCH()-funktionen söker efter en text som innehåller 'Kil' i början, 'o' i slutet och valfritt antal tecken däremellan. 'Kil*o' matchar Kilimanjaro i arrayen och därför returnerar funktionen den relativa positionen för Kilimanjaro, vilket är 16.
INDEX och MATCH
MATCH-funktioner används sällan ensamma. De parades ofta ihop med andra funktioner för att skapa kraftfulla formler. När MATCH-funktionen kombineras med INDEX-funktionen kan den utföra avancerade uppslagningar. Många människor föredrar fortfarande att använda VLOOKUP för att slå upp ett värde, eftersom det är enklare men INDEX MATCH är mer flexibelt och snabbare än VLOOKUP.
VLOOKUP kan bara slå upp ett värde vertikalt, dvs kolumner medan INDEX MATCH combo kan göra både vertikala och horisontella uppslagningar.
INDEX-funktion som används för att hämta ett värde på en specifik plats i en tabell eller ett intervall. MATCH-funktionen returnerar den relativa positionen för ett värde i en kolumn eller en rad. När den kombineras hittar MATCH rad- eller kolumnnumret (platsen) för ett specifikt värde, och INDEX-funktionen hämtar ett värde baserat på det rad- och kolumnnumret.
Syntax för INDEX-funktionen:
=INDEX(matris;radnummer;[kolumn])Låt oss i alla fall se hur INDEX MATCH fungerar med ett exempel.
I exemplet nedan vill vi hämta 'Quiz2'-poängen för studenten 'Anne'. För att göra det använder vi formeln nedan:
=INDEX(B2:F20;MATCH(H2;A2:A20;0);3)INDEX behöver ett rad- och kolumnnummer för att hämta ett värde. I formeln ovan hittar den kapslade MATCH-funktionen radnumret (positionen) för värdet "Anne" (H2). Sedan tillför vi det radnumret till INDEX-funktionen med ett intervall B2:F20 och ett kolumnnummer (3), som vi anger. Och INDEX-funktionen returnerar poängen '91'.
Tvåvägssökning med INDEX och MATCH
Du kan också använda funktionerna INDEX och MATCH för att slå upp ett värde i ett tvådimensionellt område (tvåvägsuppslag). I exemplet ovan använde vi MATCH-funktionen för att hitta radnumret för ett värde, men vi skrev in kolumnnumret manuellt. Men vi kan hitta både rad och kolumn genom att kapsla två MATCH-funktioner, en i row_num-argumentet och en annan i column_num-argumentet för INDEX-funktionen.
Använd den här formeln för en tvåvägssökning med INDEX och MATCH:
=INDEX(A1:F20,MATCH(H2,A2:A20,0),MATCH(H3,A1:F1,0))Som vi vet kan MATCH-funktionen leta efter ett värde både horisontellt och vertikalt. I den här formeln hittar den andra MATCH-funktionen i argumentet kolumn_num positionen för Quiz2 (4) och tillhandahåller den till INDEX-funktionen. Och INDEX hämtar poängen.
Nu vet du hur du använder matchningsfunktionen i Excel.