Du kan bruge Excel MATCH-funktionen til at finde den relative position af en bestemt værdi i et celleområde eller en matrix.
MATCH-funktionen ligner VLOOKUP-funktionen, da de begge er kategoriseret under Excel-opslags-/referencefunktioner. VLOOKUP søger efter en bestemt værdi i en kolonne og returnerer en værdi i samme række, mens MATCH-funktionen søger efter en bestemt værdi i et område og returnerer positionen for denne værdi.
Excel MATCH-funktionen søger efter en specificeret værdi i et celleområde eller et array og returnerer den relative position af den første forekomst af denne værdi i området. MATCH-funktionen kan også bruges til at slå en bestemt værdi op og returnere dens tilsvarende værdi ved hjælp af INDEX-funktionen (ligesom Vlookup). Lad os se, hvordan du bruger Excel MATCH-funktionen til at finde placeringen af en opslagsværdi i en række celler.
Excel MATCH funktion
MATCH-funktionen er en indbygget funktion i Excel og den bruges primært til at lokalisere den relative position af en opslagsværdi i en kolonne eller en række.
Syntaks for MATCH-funktion:
=MATCH(opslagsværdi,opslagsmatrix,[matchtype})Hvor:
opslagsværdi – Den værdi, du vil slå op i et bestemt celleområde eller i en matrix. Det kan være en numerisk værdi, tekstværdi, logisk værdi eller en cellereference, der har en værdi.
opslag_array – De arrays af celler, hvor du søger efter en værdi. Det skal være en enkelt kolonne eller en enkelt række.
match_type – Det er en valgfri parameter, der kan indstilles til 0,1 eller -1, og standarden er 1.
- 0 søger efter et nøjagtigt match, når det ikke findes, returnerer en fejl.
- -1 søger efter den mindste værdi, der er større end eller lig med opslagsværdi, når opslagsarrayet er i stigende rækkefølge.
- 1 søger efter den største værdi, der er mindre end eller lig med opslagsværdien, når opslagsarrayet er i faldende rækkefølge.
Find position for et nøjagtigt match
Lad os antage, at vi har følgende datasæt, hvor vi ønsker at finde positionen af en bestemt værdi.
I denne tabel ønsker vi at finde positionen et bynavn (Memphis) i kolonne (A2:A23), så vi bruger denne formel:
=MATCH("memphis",A2:A23,0)Det tredje argument er sat til '0', fordi vi ønsker at finde et nøjagtigt match til bynavnet. Som du kan se, er bynavnet "memphis" i formlen med små bogstaver, mens det første bogstav i bynavnet i tabellen er med stort bogstav (Memphis). Alligevel er formlen i stand til at finde positionen af den angivne værdi i det givne område. Det er fordi MATCH-funktionen er ufølsom over for store og små bogstaver.
Bemærk: Hvis lookup_value ikke findes i opslagsområdet, eller hvis du angiver det forkerte opslagsområde, returnerer funktionen #N/A fejlen.
Du kan bruge en cellereference i funktionens første argument i stedet for en direkte værdi. Formlen nedenfor finder værdiens position i celle F2 og returnerer resultatet i celle F3.
Find position for et omtrentligt match
Der er to måder, du kan søge efter en omtrentlig eller nøjagtig match af opslagsværdien og returnere dens position.
- En måde er at finde den mindste værdi, der er større end eller lig (næststørste match) med den angivne værdi. Det kan opnås ved at sætte det sidste argument (match_type) af funktionen som '-1'
- En anden måde er den største værdi, der er mindre end eller lig (næstmindste match) med den givne værdi. Det kan opnås ved at indstille match_type for funktionen som '1'
Næstmindste kamp
Hvis funktionen ikke kan finde et nøjagtigt match til den angivne værdi, når matchtypen er sat til '1', finder den den største værdi, der er lidt mindre end den angivne værdi (hvilket betyder den næstmindste værdi) og returnerer dens position . For at dette skal virke, skal du sortere arrayet i stigende rækkefølge, hvis ikke vil det resultere i en fejl.
I eksemplet bruger vi nedenstående formel til at finde det næstmindste match:
=MATCH(F2;D2:D23;1)Når denne formel ikke kunne finde det nøjagtige match for værdien i celle F2, peger den på positionen (16) for den næstmindste værdi, dvs. 98.
Næststørste kamp
Når matchtypen er sat til '-1', og MATCH-funktionen ikke kan finde et nøjagtigt match, finder den den mindste værdi, der er større end den angivne værdi (hvilket betyder den næststørste værdi) og returnerer dens position. Opslagsarrayet skal sorteres i faldende rækkefølge for denne metode, ellers vil det returnere en fejl.
Indtast f.eks. følgende formel for at finde den næststørste match til opslagsværdien:
=MATCH(F2;D2:D23;-1)Denne MATCH-funktion søger efter værdien i F2 (55) i opslagsområdet D2:D23, og når den ikke kan finde det nøjagtige match, returnerer den positionen (16) for den næststørste værdi, dvs. 58.
Wildcard Match
Jokertegn kan kun bruges i MATCH-funktionen, når match_type er sat til '0', og opslagsværdien er en tekststreng. Der er jokertegn, du kan bruge i MATCH-funktionen: en stjerne (*) og et spørgsmålstegn (?).
- Spørgsmålstegn (?) bruges til at matche et enkelt tegn eller bogstav med tekststrengen.
- Stjerne (*) bruges til at matche et vilkårligt antal tegn med strengen.
For eksempel brugte vi to '?'-jokertegn i lookup_value (Lo??n) i MATCH-funktionen til at finde en værdi, der matcher tekststrengen med to vilkårlige tegn (på jokertegnspladserne). Og funktionen returnerer den relative position af den matchende værdi i celle E5.
=MATCH("Lo??n",A2:A22,0)
Du kan bruge jokertegn (*) på samme måde som (?), men en stjerne bruges til at matche et vilkårligt antal tegn, mens et spørgsmålstegn bruges til at matche ethvert enkelt tegn.
For eksempel, hvis du bruger 'sp*', kan funktionen matche med højttaler, hastighed eller spielberg osv. Men hvis funktionen finder flere/duplikerede værdier, der matcher opslagsværdien, vil den kun returnere positionen for den første værdi.
I eksemplet indtastede vi "Kil*o" i lookup_value-argumentet. Så MATCH()-funktionen søger efter en tekst, der indeholder 'Kil' i begyndelsen, 'o' i slutningen og et hvilket som helst antal tegn derimellem. 'Kil*o' matcher Kilimanjaro i arrayet, og derfor returnerer funktionen den relative position af Kilimanjaro, som er 16.
INDEX og MATCH
MATCH-funktioner bruges sjældent alene. De parrede ofte med andre funktioner for at skabe kraftfulde formler. Når MATCH-funktionen kombineres med INDEX-funktionen, kan den udføre avancerede opslag. Mange mennesker foretrækker stadig at bruge VLOOKUP til at slå en værdi op, fordi det er enklere, men INDEX MATCH er mere fleksibel og hurtigere end VLOOKUP.
VLOOKUP kan kun slå en værdi op lodret, dvs. kolonner, mens INDEX MATCH combo kan lave både lodrette og vandrette opslag.
INDEX-funktion, der bruges til at hente en værdi på et bestemt sted i en tabel eller et område. MATCH-funktionen returnerer den relative position af en værdi i en kolonne eller en række. Når den kombineres, finder MATCH række- eller kolonnenummeret (placeringen) for en bestemt værdi, og INDEX-funktionen henter en værdi baseret på det pågældende række- og kolonnenummer.
Syntaks for INDEX-funktionen:
=INDEKS(matrix;rækkenummer;[kolonnetal])Lad os alligevel se, hvordan INDEX MATCH fungerer med et eksempel.
I eksemplet nedenfor ønsker vi at hente 'Quiz2'-score for eleven 'Anne'. For at gøre det bruger vi nedenstående formel:
=INDEKS(B2:F20;MATCH(H2;A2:A20;0);3)INDEX har brug for et række- og kolonnenummer for at hente en værdi. I ovenstående formel finder den indlejrede MATCH-funktion rækkenummeret (positionen) for værdien 'Anne' (H2). Så leverer vi det rækkenummer til INDEX-funktionen med et område B2:F20 og et kolonnenummer (3), som vi angiver. Og INDEX-funktionen returnerer scoren '91'.
To-vejs opslag med INDEX og MATCH
Du kan også bruge funktionerne INDEX og MATCH til at slå en værdi op i et todimensionalt område (to-vejs opslag). I ovenstående eksempel brugte vi MATCH-funktionen til at finde rækkenummeret for en værdi, men vi indtastede kolonnenummeret manuelt. Men vi kan finde både række og kolonne ved at indlejre to MATCH-funktioner, en i rækkenummer-argumentet og en anden i kolonnenummer-argumentet for INDEX-funktionen.
Brug denne formel til et to-vejs opslag med INDEX og MATCH:
=INDEKS(A1:F20;MATCH(H2;A2:A20,0);MATCH(H3;A1:F1;0))Som vi ved, kan MATCH-funktionen søge efter en værdi både vandret og lodret. I denne formel finder den anden MATCH-funktion i argumentet colum_num positionen for Quiz2 (4) og leverer den til INDEX-funktionen. Og INDEX henter scoren.
Nu ved du, hvordan du bruger Match-funktionen i Excel.