Sådan bruges INDEX- og MATCH-funktionen i Excel

2024 Forfatter: Abigail Brown | [email protected]. Sidst ændret: 2023-12-17 06:43

Hvad du skal vide

INDEX-funktionen kan bruges alene, men indlejring af MATCH-funktionen inde i den skaber et avanceret opslag.
Denne indlejrede funktion er mere fleksibel end VLOOKUP og kan give resultater hurtigere.

Denne artikel forklarer, hvordan du bruger funktionerne INDEX og MATCH sammen i alle versioner af Excel, inklusive Excel 2019 og Microsoft 365.

Hvad er INDEX- og MATCH-funktionerne?

INDEX og MATCH er Excel-opslagsfunktioner. Selvom de er to helt separate funktioner, der kan bruges hver for sig, kan de også kombineres for at skabe avancerede formler.

Funktionen INDEX returnerer en værdi eller referencen til en værdi fra et bestemt valg. Det kan f.eks. bruges til at finde værdien i anden række af et datasæt eller i den femte række og tredje kolonne.

Mens INDEX meget vel kunne bruges alene, gør indlejring af MATCH i formlen den en smule mere nyttig. MATCH-funktionen søger efter et specificeret element i et celleområde og returnerer derefter elementets relative position i området. Det kunne f.eks. bruges til at bestemme, at et specifikt navn er det tredje punkt på en liste over navne.

INDEX og MATCH-syntaks og -argumenter

Sådan skal begge funktioner skrives, for at Excel kan forstå dem:

=INDEX(array, row_num, [column_num])

array er det celleområde, som formlen vil bruge. Det kan være en eller flere rækker og kolonner, såsom A1:D5. Det er påkrævet.
row_num er rækken i arrayet, hvorfra der skal returneres en værdi, såsom 2 eller 18. Det er påkrævet, medmindre column_num er til stede.
column_num er kolonnen i arrayet, hvorfra der skal returneres en værdi, såsom 1 eller 9. Det er valgfrit.

=MATCH(lookup_value, lookup_array, [match_type])

lookup_value er den værdi, du vil matche i lookup_array. Det kan være et tal, en tekst eller en logisk værdi, der er indtastet manuelt eller refereret til via en cellereference. Dette er påkrævet.
lookup_array er rækken af celler, der skal kigges igennem. Det kan være en enkelt række eller en enkelt kolonne, såsom A2:D2 eller G1:G45. Dette er påkrævet.
match_type kan være -1, 0 eller 1. Det specificerer, hvordan lookup_value matches med værdier i lookup_array (se nedenfor). 1 er standardværdien, hvis dette argument er udeladt.

Hvilken matchtype skal bruges
Match Type	What It Does	Regel	Eksempel
1	Finder den største værdi, der er mindre end eller lig med lookup_value.	Lookup_array-værdierne skal placeres i stigende rækkefø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å positionen for det næstmindste tal, f.eks. 22, returneres i stedet.
0	Finder den første værdi, der er nøjagtigt lig med lookup_value.	lookup_array-værdierne kan være i enhver rækkefølge.	lookup_value er 25, så det returnerer positionen 25.
-1	Finder den mindste værdi, der er større eller lig med lookup_value.	Lookup_array-værdierne skal placeres i faldende rækkefølge (f.eks. 2, 1, 0, -1, -2).	lookup_value er 25, men den mangler fra lookup_array, så positionen for det næststørste tal, som 34, returneres i stedet.

Brug 1 eller -1 til tidspunkter, hvor du har brug for at køre et omtrentligt opslag langs en skala, f.eks. når du har at gøre med tal, og når tilnærmelser er i orden. Men husk, at hvis du ikke angiver match_type, vil 1 være standard, hvilket kan skævvride resultaterne, hvis du virkelig ønsker et nøjagtigt match.

Eksempel på INDEX- og MATCH-formler

Før vi ser på, hvordan man kombinerer INDEX og MATCH i én formel, skal vi forstå, hvordan disse funktioner fungerer alene.

INDEX-eksempler

=INDEX(A1:B2, 2, 2)

=INDEX(A1:B1, 1)

=INDEX(2:2, 1)=INDEX(B1:B2; 1)

I dette første eksempel er der fire INDEX-formler, vi kan bruge til at få forskellige værdier:

=INDEX(A1:B2, 2, 2) ser gennem A1:B2 for at finde værdien i anden kolonne og anden række, som er Stacy.
=INDEX(A1:B1, 1) ser gennem A1:B1 for at finde værdien i den første kolonne, som er Jon.
=INDEX(2:2, 1) gennemgår alt i anden række for at finde værdien i den første kolonne, som er Tim.
=INDEX(B1:B2, 1) ser gennem B1:B2 for at finde værdien i den første række, som er Amy.

MATCH-eksempler

=MATCH("Stacy", A2:D2, 0)

=MATCH(14, D1:D2)

=MATCH(14, D1:D2, -1)=MATCH(13, A1:D1, 0)

Her er fire nemme eksempler på MATCH-funktionen:

=MATCH("Stacy", A2:D2, 0) søger efter Stacy i området A2:D2 og returnerer 3 som resultat.
=MATCH(14, D1:D2) søger efter 14 i området D1:D2, men da det ikke findes i tabellen, finder MATCH den næststørste værdi det er mindre end eller lig med 14, hvilket i dette tilfælde er 13, som er i position 1 i lookup_array.
=MATCH(14, D1:D2, -1) er identisk med formlen over den, men da arrayet ikke er i faldende rækkefølge, som -1 kræver, vi får en fejl.
=MATCH(13, A1:D1, 0) leder efter 13 i den første række af arket, hvilket returnerer 4, da det er det fjerde element i denne matrix.

INDEX-MATCH-eksempler

Her er to eksempler, hvor vi kan kombinere INDEX og MATCH i én formel:

Find cellereference i tabel

=INDEX(B2:B5, MATCH(F1, A2:A5))

Dette eksempel indlejrer MATCH-formlen i INDEX-formlen. Målet er at identificere varens farve ved hjælp af varenummeret.

Hvis du ser på billedet, kan du se i de "Separerede" rækker, hvordan formlerne ville blive skrevet på egen hånd, men da vi indlejrer dem, er dette, hvad der sker:

MATCH(F1, A2:A5) leder efter F1-værdien (8795) i datasættet A2:A5. Hvis vi tæller kolonnen ned, kan vi se, at den er 2, så det er, hvad MATCH-funktionen lige har fundet ud af.
INDEX-arrayet er B2:B5, da vi i sidste ende leder efter værdien i den kolonne.
INDEX-funktionen kunne nu omskrives på denne måde, da 2 er, hvad MATCH fandt: INDEX(B2:B5, 2, [column_num]).
Da column_num er valgfrit, kan vi fjerne det for at blive tilbage med dette: INDEX(B2:B5, 2).
Så nu er dette som en normal INDEX-formel, hvor vi finder værdien af det andet element i B2:B5, som er rødt.

Opslag efter række- og kolonneoverskrifter

=INDEX(B2:E13, MATCH(G1, A2:A13, 0), MATCH(G2, B1:E1, 0))

I dette eksempel med MATCH og INDEX laver vi et to-vejs opslag. Idéen er at se, hvor mange penge vi tjente på grønne varer i maj. Dette ligner virkelig eksemplet ovenfor, men en ekstra MATCH-formel er indlejret i INDEX.

MATCH(G1, A2:A13, 0) er det første punkt, der løses i denne formel. Den leder efter G1 (ordet "maj") i A2:A13 for at få en bestemt værdi. Vi kan ikke se det her, men det er 5.
MATCH(G2, B1:E1, 0) er den anden MATCH-formel, og den ligner virkelig den første, men leder i stedet efter G2 (ordet "Grøn") i kolonneoverskrifterne ved B1:E1. Denne løses til 3.
Vi kan nu omskrive INDEX-formlen sådan her for at visualisere, hvad der sker: =INDEX(B2:E13, 5, 3). Dette ser i hele tabellen, B2:E13, efter den femte række og tredje kolonne, som returnerer $180.

MATCH og INDEX-regler

Der er flere ting, du skal huske på, når du skriver formler med disse funktioner:

MATCH skelner ikke mellem store og små bogstaver, så store og små bogstaver behandles ens, når tekstværdier matches.
MATCH returnerer N/A af flere årsager: hvis match_type er 0 og lookup_value ikke findes hvis match_type er -1 og lookup_array ikke er i faldende rækkefølge, hvis match_type er 1 og lookup_array ikke er i stigende rækkefølge rækkefølge, og hvis lookup_array ikke er en enkelt række eller kolonne.
Du kan bruge et jokertegn i lookup_value argumentet, hvis match_type er 0 og lookup_value er en tekststreng. Et spørgsmålstegn matcher ethvert enkelt tegn, og en stjerne matcher enhver sekvens af tegn (f.f.eks. =MATCH("Jo", 1:1, 0)). For at bruge MATCH til at finde et egentligt spørgsmålstegn eller en stjerne, skriv ~ først.
INDEX returnerer REF! hvis row_num og column_num ikke peger på en celle i matrixen.

Relaterede Excel-funktioner

MATCH-funktionen ligner OPSLAG, men MATCH returnerer varens position i stedet for selve elementet.

VLOOKUP er en anden opslagsfunktion, du kan bruge i Excel, men i modsætning til MATCH, som kræver INDEX for avancerede opslag, behøver VLOOKUP-formler kun den ene funktion.