Du kan bruke Excel MATCH-funksjonen til å finne den relative plasseringen til en bestemt verdi i et celleområde eller en matrise.
MATCH-funksjonen ligner på VLOOKUP-funksjonen da de begge er kategorisert under Excel-oppslag/referansefunksjoner. VLOOKUP søker etter en bestemt verdi i en kolonne og returnerer en verdi i samme rad mens MATCH-funksjonen søker etter en bestemt verdi i et område og returnerer posisjonen til den verdien.
Excel MATCH-funksjonen ser etter en spesifisert verdi i et celleområde eller en matrise og returnerer den relative plasseringen til den første opptredenen av den verdien i området. MATCH-funksjonen kan også brukes til å slå opp en bestemt verdi og returnere dens tilsvarende verdi ved hjelp av INDEX-funksjonen (akkurat som Vlookup). La oss se hvordan du bruker Excel MATCH-funksjonen for å finne plasseringen til en oppslagsverdi i en rekke celler.
Excel MATCH-funksjon
MATCH-funksjonen er en innebygd funksjon i Excel og den brukes først og fremst for å finne den relative plasseringen til en oppslagsverdi i en kolonne eller en rad.
Syntaks for MATCH-funksjonen:
=MATCH(oppslagsverdi,oppslagsmatrise,[samsvarstype})Hvor:
oppslagsverdi – Verdien du vil slå opp i et spesifisert celleområde eller i en matrise. Det kan være en numerisk verdi, tekstverdi, logisk verdi eller en cellereferanse som har en verdi.
lookup_array – Matrisene med celler der du søker etter en verdi. Det må være en enkelt kolonne eller en enkelt rad.
match_type – Det er en valgfri parameter som kan settes til 0,1 eller -1 og standard er 1.
- 0 ser etter et eksakt samsvar, når det ikke blir funnet, returnerer det en feil.
- -1 ser etter den minste verdien som er større enn eller lik lookup_value når oppslagsmatrisen i stigende rekkefølge.
- 1 ser etter den største verdien som er mindre enn eller lik oppslagsverdien når oppslagsmatrisen i synkende rekkefølge.
Finn plasseringen av en eksakt match
La oss anta at vi har følgende datasett der vi ønsker å finne posisjonen til en viss verdi.
I denne tabellen ønsker vi å finne posisjonen et bynavn (Memphis) i kolonne (A2:A23), så vi bruker denne formelen:
=MATCH("memphis",A2:A23,0)Det tredje argumentet er satt til '0' fordi vi ønsker å finne et eksakt samsvar med bynavnet. Som du kan se at bynavnet "memphis" i formelen er med små bokstaver, mens i tabellen står den første bokstaven i bynavnet med stor bokstav (Memphis). Fortsatt er formelen i stand til å finne posisjonen til den angitte verdien i det gitte området. Det er fordi MATCH-funksjonen ikke skiller mellom store og små bokstaver.
Merk: Hvis oppslagsverdien ikke finnes i oppslagsområdet eller hvis du spesifiserer feil oppslagsområde, vil funksjonen returnere #N/A-feilen.
Du kan bruke en cellereferanse i det første argumentet til funksjonen i stedet for en direkte verdi. Formelen nedenfor finner plasseringen av verdien i celle F2 og returnerer resultatet i celle F3.
Finn plasseringen av en omtrentlig match
Det er to måter du kan se etter et omtrentlig eller eksakt samsvar med oppslagsverdien og returnere dens posisjon.
- En måte er å finne den minste verdien som er større enn eller lik (nest største samsvar) med den angitte verdien. Det kan oppnås ved å sette det siste argumentet (match_type) til funksjonen som '-1'
- En annen måte er den største verdien som er mindre enn eller lik (nest minste samsvar) med den gitte verdien. Det kan oppnås ved å sette match_type for funksjonen som '1'
Nest minste kamp
Hvis funksjonen ikke finner et eksakt samsvar med den angitte verdien når samsvartypen er satt til '1', finner den den største verdien som er litt mindre enn den spesifiserte verdien (som betyr den nest minste verdien) og returnerer sin posisjon . For at dette skal fungere, må du sortere matrisen i stigende rekkefølge, hvis ikke vil det resultere i en feil.
I eksemplet bruker vi formelen nedenfor for å finne den nest minste matchen:
=MATCH(F2;D2:D23;1)Når denne formelen ikke fant nøyaktig samsvar for verdien i celle F2, peker den på posisjonen (16) til den nest minste verdien, dvs. 98.
Nest største kamp
Når samsvarstypen er satt til '-1' og MATCH-funksjonen ikke finner et eksakt samsvar, finner den den minste verdien som er større enn den spesifiserte verdien (som betyr den nest største verdien) og returnerer sin posisjon. Oppslagsmatrisen må sorteres i synkende rekkefølge for denne metoden, ellers vil den returnere en feil.
Angi for eksempel følgende formel for å finne det nest største samsvaret med oppslagsverdien:
=MATCH(F2;D2:D23;-1)Denne MATCH-funksjonen søker etter verdien i F2 (55) i oppslagsområdet D2:D23, og når den ikke finner det eksakte samsvaret, returnerer den posisjonen (16) til den nest største verdien, dvs. 58.
Wildcard Match
Jokertegn kan bare brukes i MATCH-funksjonen når match_type er satt til '0' og oppslagsverdien er en tekststreng. Det er jokertegn du kan bruke i MATCH-funksjonen: en stjerne (*) og et spørsmålstegn (?).
- Spørsmålstegn (?) brukes til å matche et enkelt tegn eller bokstav med tekststrengen.
- Stjerne (*) brukes til å matche et hvilket som helst antall tegn med strengen.
For eksempel brukte vi to «?»-jokertegn i lookup_value (Lo??n) til MATCH-funksjonen for å finne en verdi som samsvarer med tekststrengen med to vilkårlige tegn (i jokertegnsplasseringene). Og funksjonen returnerer den relative posisjonen til den samsvarende verdien i celle E5.
=MATCH("Lo??n",A2:A22,0)
Du kan bruke jokertegn (*) på samme måte som (?), men en stjerne brukes for å matche et hvilket som helst antall tegn mens et spørsmålstegn brukes til å matche et enkelt tegn.
For eksempel, hvis du bruker 'sp*', kan funksjonen samsvare med høyttaler, hastighet eller spielberg, etc. Men hvis funksjonen finner flere/dupliserte verdier som samsvarer med oppslagsverdien, vil den bare returnere posisjonen til den første verdien.
I eksemplet skrev vi inn "Kil*o" i oppslagsverdi-argumentet. Så MATCH()-funksjonen søker etter en tekst som inneholder 'Kil' i begynnelsen, 'o' på slutten, og et hvilket som helst antall tegn i mellom. 'Kil*o' samsvarer med Kilimanjaro i matrisen, og derfor returnerer funksjonen den relative plasseringen til Kilimanjaro, som er 16.
INDEKS og MATCH
MATCH-funksjoner brukes sjelden alene. De paret seg ofte med andre funksjoner for å lage kraftige formler. Når MATCH-funksjonen er kombinert med INDEX-funksjonen, kan den utføre avanserte oppslag. Mange foretrekker fortsatt å bruke VLOOKUP for å slå opp en verdi, fordi det er enklere, men INDEX MATCH er mer fleksibel og raskere enn VLOOKUP.
VLOOKUP kan bare slå opp en verdi vertikalt, dvs. kolonner mens INDEX MATCH combo kan gjøre både vertikale og horisontale oppslag.
INDEX-funksjon som brukes til å hente en verdi på et bestemt sted i en tabell eller et område. MATCH-funksjonen returnerer den relative plasseringen til en verdi i en kolonne eller en rad. Når den kombineres, finner MATCH rad- eller kolonnenummeret (plasseringen) for en bestemt verdi, og INDEX-funksjonen henter en verdi basert på det rad- og kolonnenummeret.
Syntaks for INDEX-funksjonen:
=INDEKS(matrise;radnummer;[kolonnetall],)La oss uansett se hvordan INDEX MATCH fungerer med et eksempel.
I eksemplet nedenfor ønsker vi å hente «Quiz2»-poengsummen for eleven «Anne». For å gjøre det bruker vi formelen nedenfor:
=INDEKS(B2:F20;MATCH(H2;A2:A20;0);3)INDEX trenger et rad- og kolonnenummer for å hente en verdi. I formelen ovenfor finner den nestede MATCH-funksjonen radnummeret (posisjonen) til verdien 'Anne' (H2). Deretter leverer vi det radnummeret til INDEX-funksjonen med et område B2:F20 og et kolonnenummer (3), som vi spesifiserer. Og INDEX-funksjonen returnerer poengsummen '91'.
Toveis oppslag med INDEX og MATCH
Du kan også bruke funksjonene INDEX og MATCH til å slå opp en verdi i et todimensjonalt område (toveis oppslag). I eksemplet ovenfor brukte vi MATCH-funksjonen for å finne radnummeret til en verdi, men vi skrev inn kolonnenummeret manuelt. Men vi kan finne både rad og kolonne ved å neste to MATCH-funksjoner, en i row_num-argumentet og en annen i column_num-argumentet til INDEX-funksjonen.
Bruk denne formelen for et toveis oppslag med INDEX og MATCH:
=INDEKS(A1:F20;MATCH(H2;A2:A20;0),MATCH(H3;A1:F1;0))Som vi vet kan MATCH-funksjonen se etter en verdi både horisontalt og vertikalt. I denne formelen finner den andre MATCH-funksjonen i colum_num-argumentet posisjonen til Quiz2 (4) og leverer den til INDEX-funksjonen. Og INDEKS henter poengsummen.
Nå vet du hvordan du bruker Match-funksjonen i Excel.