INDEX-funksjonen kan brukes alene, men å legge MATCH-funksjonen inne i den skaper et avansert oppslag.
Denne nestede funksjonen er mer fleksibel enn VLOOKUP og kan gi resultater raskere.
Denne artikkelen forklarer hvordan du bruker funksjonene INDEX og MATCH sammen i alle versjoner av Excel, inkludert Excel 2019 og Microsoft 365.
Hva er INDEX- og MATCH-funksjonene?
INDEX og MATCH er Excel-oppslagsfunksjoner. Selv om de er to helt separate funksjoner som kan brukes på egen hånd, kan de også kombineres for å lage avanserte formler.
INDEKS-funksjonen returnerer en verdi eller referansen til en verdi fra et bestemt utvalg. Den kan for eksempel brukes til å finne verdien i den andre raden i et datasett, eller i den femte raden og den tredje kolonnen.
Selv om INDEX godt kan brukes alene, gjør neste MATCH i formelen den litt mer nyttig. MATCH-funksjonen søker etter et spesifisert element i et celleområde og returnerer deretter den relative plasseringen til elementet i området. Det kan for eksempel brukes til å fastslå at et spesifikt navn er det tredje elementet i en navneliste.
Image
INDEX and MATCH-syntaks og argumenter
Slik må begge funksjonene skrives for at Excel skal forstå dem:
=INDEX(array, row_num, [column_num])
array er celleområdet som formelen skal bruke. Det kan være én eller flere rader og kolonner, for eksempel A1:D5. Det er påkrevd.
row_num er raden i matrisen som en verdi skal returneres fra, for eksempel 2 eller 18. Den er påkrevd med mindre column_num er til stede.
column_num er kolonnen i matrisen som en verdi skal returneres fra, for eksempel 1 eller 9. Det er valgfritt.
=MATCH(lookup_value, lookup_array, [match_type])
lookup_value er verdien du vil matche i lookup_array. Det kan være et tall, en tekst eller en logisk verdi som er skrevet inn manuelt eller referert til via en cellereferanse. Dette er påkrevd.
lookup_array er celleområdet som skal ses gjennom. Det kan være en enkelt rad eller en enkelt kolonne, for eksempel A2:D2 eller G1:G45. Dette er påkrevd.
match_type kan være -1, 0 eller 1. Den spesifiserer hvordan lookup_value matches med verdier i lookup_array (se nedenfor). 1 er standardverdien hvis dette argumentet er utelatt.
Hvilken samsvartype å bruke
Samsvartype
What It Does
Regel
Eksempel
1
Finner den største verdien som er mindre enn eller lik lookup_value.
Lookup_array-verdiene må plasseres i stigende rekkefølge (f.eks. -2, -1, 0, 1, 2; eller A-Z;, eller FALSE, TRUE.
lookup_value er 25, men den mangler fra lookup_array, så posisjonen til det nest minste tallet, som 22, returneres i stedet.
0
Finner den første verdien som er nøyaktig lik lookup_value.
lookup_array-verdiene kan være i hvilken som helst rekkefølge.
lookup_value er 25, så den returnerer posisjonen 25.
-1
Finner den minste verdien som er større eller lik lookup_value.
lookup_array-verdiene må plasseres i synkende rekkefølge (f.eks. 2, 1, 0, -1, -2).
lookup_value er 25, men den mangler fra lookup_array, så posisjonen til det nest største tallet, som 34, returneres i stedet.
Bruk 1 eller -1 for tidspunkter når du trenger å kjøre et omtrentlig oppslag langs en skala, som når du arbeider med tall og når tilnærminger er i orden. Men husk at hvis du ikke spesifiserer match_type, vil 1 være standard, noe som kan skjeve resultatene hvis du virkelig ønsker et eksakt samsvar.
Eksempel på INDEX- og MATCH-formler
Før vi ser på hvordan vi kombinerer INDEX og MATCH til én formel, må vi forstå hvordan disse funksjonene fungerer på egen hånd.
INDEX-eksempler
=INDEKS(A1:B2, 2, 2)
=INDEKS(A1:B1, 1)
=INDEKS(2:2, 1)=INDEKS(B1:B2; 1)
Image
I dette første eksempelet er det fire INDEKS-formler vi kan bruke for å få forskjellige verdier:
=INDEKS(A1:B2, 2, 2) ser gjennom A1:B2 for å finne verdien i den andre kolonnen og andre raden, som er Stacy.
=INDEKS(A1:B1, 1) ser gjennom A1:B1 for å finne verdien i den første kolonnen, som er Jon.
=INDEKS(2:2, 1) ser gjennom alt i den andre raden for å finne verdien i den første kolonnen, som er Tim.
=INDEKS(B1:B2, 1) ser gjennom B1:B2 for å finne verdien i den første raden, som er Amy.
MATCH-eksempler
=MATCH("Stacy", A2:D2, 0)
=MATCH(14, D1:D2)
=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)
Image
Her er fire enkle eksempler på MATCH-funksjonen:
=MATCH("Stacy", A2:D2, 0) søker etter Stacy i området A2:D2 og returnerer 3 som resultat.
=MATCH(14, D1:D2) søker etter 14 i området D1:D2, men siden det ikke finnes i tabellen, finner MATCH den nest største verdien som er mindre enn eller lik 14, som i dette tilfellet er 13, som er i posisjon 1 av lookup_array.
=MATCH(14, D1:D2, -1) er identisk med formelen over den, men siden matrisen ikke er i synkende rekkefølge som -1 krever, vi får en feil.
=MATCH(13, A1:D1, 0) ser etter 13 i den første raden av arket, som returnerer 4 siden det er det fjerde elementet i denne matrisen.
INDEX-MATCH-eksempler
Her er to eksempler der vi kan kombinere INDEX og MATCH i én formel:
Finn cellereferanse i tabell
=INDEKS(B2:B5, MATCH(F1, A2:A5))
Image
Dette eksemplet legger MATCH-formelen i INDEX-formelen. Målet er å identifisere varefargen ved å bruke varenummeret.
Hvis du ser på bildet, kan du se i "Separert"-radene hvordan formlene vil bli skrevet på egen hånd, men siden vi nester dem, er dette hva som skjer:
MATCH(F1, A2:A5) ser etter F1-verdien (8795) i datasettet A2:A5. Hvis vi teller ned kolonnen, kan vi se at den er 2, så det var det MATCH-funksjonen fant ut.
INDEX-matrisen er B2:B5 siden vi til slutt ser etter verdien i den kolonnen.
INDEX-funksjonen kan nå skrives om slik siden 2 er det MATCH fant: INDEX(B2:B5, 2, [column_num]).
Siden column_num er valgfritt, kan vi fjerne det for å stå igjen med dette: INDEX(B2:B5, 2).
Så nå er dette som en vanlig INDEKS-formel der vi finner verdien av det andre elementet i B2:B5, som er rødt.
I dette eksemplet med MATCH og INDEX, gjør vi et toveis oppslag. Tanken er å se hvor mye penger vi tjente på grønne varer i mai. Dette ligner egentlig på eksemplet ovenfor, men en ekstra MATCH-formel er nestet i INDEX.
MATCH(G1, A2:A13, 0) er det første elementet som er løst i denne formelen. Den leter etter G1 (ordet "mai") i A2:A13 for å få en bestemt verdi. Vi ser det ikke her, men det er 5.
MATCH(G2, B1:E1, 0) er den andre MATCH-formelen, og den er egentlig lik den første, men leter i stedet etter G2 (ordet "Grønn") i kolonneoverskriftene ved B1:E1. Denne løses til 3.
Vi kan nå omskrive INDEX-formelen slik for å visualisere hva som skjer: =INDEX(B2:E13, 5, 3). Dette ser i hele tabellen, B2:E13, etter den femte raden og den tredje kolonnen, som returnerer $180.
MATCH and INDEX-regler
Det er flere ting du må huske på når du skriver formler med disse funksjonene:
MATCH skiller ikke mellom store og små bokstaver, så store og små bokstaver behandles likt når tekstverdier samsvarer.
MATCH returnerer N/A av flere årsaker: hvis match_type er 0 og lookup_value ikke finnes hvis match_type er -1 og lookup_array ikke er i synkende rekkefølge, hvis match_type er 1 og lookup_array ikke er i stigende rekkefølge, og hvis lookup_array ikke er en enkelt rad eller kolonne.
Du kan bruke et jokertegn i lookup_value-argumentet hvis match_type er 0 og lookup_value er en tekststreng. Et spørsmålstegn samsvarer med et enkelt tegn og en stjerne samsvarer med en hvilken som helst sekvens av tegn (f.f.eks. =MATCH("Jo", 1:1, 0)). For å bruke MATCH til å finne et faktisk spørsmålstegn eller en stjerne, skriv inn ~ først.
INDEX returnerer REF! hvis rad_nummer og kolonnenummer ikke peker til en celle i matrisen.
Relaterte Excel-funksjoner
MATCH-funksjonen ligner på LOOKUP, men MATCH returnerer posisjonen til elementet i stedet for selve elementet.
VLOOKUP er en annen oppslagsfunksjon du kan bruke i Excel, men i motsetning til MATCH som krever INDEX for avanserte oppslag, trenger VLOOKUP-formler bare den ene funksjonen.
Kombiner funksjonene INDIREKTE og COUNTIF for å telle et dynamisk område avhengig av resultatene av et HVIS-argument. Oppdatert til å inkludere Excel 2019
Når du har en liste over datoer, bruk MONTH-formelen i Excel for å trekke ut serienummeret for måneden, og gjør deretter det nummeret til månedens navn
