I Excel er nøjagtig hentning af specifikke data ofte en hyppig nødvendighed. Mens funktionerne INDEX og MATCH hver især har deres egne styrker, låser kombinationen dem op for et kraftfuldt værktøjssæt til dataopslag. Tilsammen letter de en række søgemuligheder, fra grundlæggende horisontale og lodrette opslag til mere avancerede funktioner som to-vejs, case-sensitive og multi-kriterie søgninger. Tilbyder forbedrede muligheder sammenlignet med VLOOKUP, parringen af ​​INDEX og MATCH giver mulighed for en bredere række af dataopslagsmuligheder. Lad os i denne tutorial dykke ned i dybden af ​​muligheder, de kan opnå sammen.

• INDEX funktion
• MATCH-funktion
• Kombiner INDEX og MATCH

• To-vejs opslag
• Venstre opslag
• Kuffertfølsom opslag
• Nærmeste match
• Opslag med flere kriterier
• Slå op på tværs af flere kolonner
• Søg efter første ikke-blanke værdi
• Søg efter første numeriske værdi
• Søg efter MAX eller MIN tilknytninger
• Tip: Skræddersy dine egne #N/A fejlmeddelelser

Sådan bruger du INDEX og MATCH i Excel

Før vi bruger funktionerne INDEX og MATCH, så lad os sikre os, at vi ved, hvordan INDEX og MATCH kan hjælpe os med at slå værdier op først.

Sådan bruges INDEX-funktionen i Excel

INDEX funktion i Excel returnerer værdien på et givet sted i et bestemt område. Syntaksen for INDEX -funktionen er som følger:

• matrix (påkrævet) refererer til det område, hvorfra du vil returnere værdien.
• række_nummer (påkrævet, medmindre kolonne_nummer er til stede) refererer til rækkenummeret for arrayet.
• kolonne_nummer (valgfrit, men påkrævet hvis række_nummer er udeladt) henviser til arrayets kolonnenummer.

For eksempel at vide score af Jeff, 6elev på listen, kan du bruge INDEX-funktionen på denne måde:

=INDEX(C2:C11,6)

√ Bemærk: Rækkevidden C2: C11 er, hvor scorerne er anført, mens antallet 6 finder eksamensresultatet for 6elev.

Lad os her lave en lille test. For formlen =INDEKS(A1:C1,2;XNUMX), hvilken værdi vil det returnere? --- Ja, den vender tilbage Fødselsdato, 2nd værdi i den givne række.

Nu bør vi vide, at INDEX-funktionen kan fungere perfekt med vandrette eller lodrette områder. Men hvad hvis vi har brug for det til at returnere en værdi i et større område med flere rækker og kolonner? Nå, i dette tilfælde skal vi anvende både et rækkenummer og et kolonnenummer. For eksempel for at finde ud af det Jeffs score inden for tabelområdet i stedet for en enkelt kolonne, kan vi lokalisere hans score med en række nummer 6 og en kolonne nummer 3 i celler gennem A2 til C11 sådan her:

=INDEX(A2:C11,6,3)

Men for en rigtig stor database med flere rækker og kolonner er det bestemt ikke praktisk for os at anvende formlen med et nøjagtigt rækkenummer og kolonnenummer. Og det er det, når vi skal kombinere brugen af ​​MATCH-funktionen.

Fantastiske! Brug af effektive faner i Excel som Chrome, Edge, Firefox og Safari!
Spar 50% af din tid, og reducer tusindvis af museklik for dig hver dag!

Sådan bruges MATCH-funktionen i Excel

MATCH -funktionen i Excel returnerer en numerisk værdi, placeringen af ​​et bestemt element i det givne område. Syntaksen for funktionen MATCH er som følger:

• opslagsværdi (påkrævet) henviser til den værdi, der skal matches i opslag_array.
• opslag_array (påkrævet) refererer til det område af celler, hvor du vil MATCH skal søge.
• match_type (valgfri): 1, 0 or -1.
  • 1 (standard), finder MATCH den største værdi, der er mindre end eller lig med opslagsværdi. Værdierne i opslag_array skal placeres i stigende rækkefølge.
  • 0, vil MATCH finde den første værdi, der nøjagtigt svarer til opslagsværdi. Værdierne i opslag_array kan være i enhver rækkefølge. (I de tilfælde, hvor matchtypen er sat til 0, kan du bruge jokertegn.)
  • -1, MATCH finder den mindste værdi, der er større end eller lig med opslagsværdi. Værdierne i opslag_array skal placeres i faldende rækkefølge.

For eksempel at vide positionen af ​​Vera på listen over navne, kan du bruge MATCH-formlen sådan her:

=MATCH("Vera",A2:A11,0)

√ Bemærk: Resultatet "4" angiver, at navnet "Vera" er på den 4. plads på listen.

Nu hvor vi kender til de grundlæggende anvendelser for INDEX- og MATCH -funktionerne i Excel, lad os rulle ærmerne op og gøre os klar til at kombinere de to funktioner.

Sådan kombinerer du INDEX og MATCH i Excel

Se venligst eksemplet nedenfor for at finde ud af, hvordan vi kan kombinere funktionerne INDEX og MATCH:

At finde Evelyns score, med viden om, at eksamensresultaterne er i 3rd kolonne, kan vi brug MATCH-funktionen til automatisk at bestemme rækkepositionen uden at skulle tælle det manuelt. Efterfølgende kan vi bruge INDEX-funktionen til at hente værdi i skæringspunktet mellem den identificerede række og 3. kolonne:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

Nå, da formlen kan se lidt kompliceret ud, lad os gennemgå hver del af den.

INDEX formlen indeholder tre argumenter:

• række_nummer: MATCH("Evelyn",A2:A11,0) giver INDEX rækkepositionen for værdien "Evelyn" i rækken A2: A11, Hvilket er 5.
• kolonne_nummer: 3 specificerer 3rd kolonne for INDEX for at lokalisere partituret i arrayet.
• matrix: A2: C11 instruerer INDEX om at returnere den matchende værdi ved skæringspunktet mellem den angivne række og kolonne, inden for intervallet fra A2 til C11. Endelig får vi resultatet 90.

I ovenstående formel brugte vi en hårdkodet værdi, "Evelyn". Men i praksis er hårdkodede værdier upraktiske, da de ville kræve modifikation, hver gang vi søger efter forskellige data, såsom scoren for en anden elev. I sådanne scenarier kan vi bruge cellereferencer til at skabe dynamiske formler. For eksempel vil jeg i dette tilfælde ændre "Evelyn" til F2:

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX og MATCH formler eksempler

I denne del vil vi tale om forskellige omstændigheder for at bruge funktionerne INDEX og MATCH til at opfylde forskellige behov.

INDEX og MATCH for at anvende et tovejsopslag

I det forrige eksempel kendte vi kolonnenummeret og brugte en MATCH-formel til at finde rækkenummeret. Men hvad nu hvis vi også er usikre på kolonnenummeret?

I sådanne tilfælde kan vi udføre et to-vejs opslag, også kendt som et matrixopslag, ved at bruge to MATCH-funktioner: den ene til at finde rækkenummeret og den anden til at bestemme kolonnenummeret. For eksempel at vide Evelyns score, skal vi bruge formlen:

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

INDEX og MATCH for at anvende et venstre opslag

Lad os nu overveje et scenario, hvor du skal bestemme Evelyns klasse. Du har måske bemærket, at klassekolonnen er placeret til venstre for navnekolonnen, en situation, der overstiger mulighederne for en anden kraftfuld Excel-opslagsfunktion, VLOOKUP.

Faktisk er muligheden for at udføre venstresideopslag et af de aspekter, hvor kombinationen af ​​INDEX og MATCH overstråler VLOOKUP.

At finde Evelyns klasse, anvend følgende formel til søg efter Evelyn i B2:B11 , hent den tilsvarende værdi fra A2:A11.

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

INDEX og MATCH for at anvende et opslagsfølsomt opslag

MATCH-funktionerne er i sagens natur ufølsomme for store og små bogstaver. Men når du kræver, at din formel skelner mellem store og små bogstaver, kan du forbedre den ved at inkorporere EKSAKT fungere. Ved at kombinere MATCH-funktionen med EXACT i en INDEX-formel kan du effektivt udføre et opslagsfølsomt opslag, som vist nedenfor:

• matrix refererer til det område, hvorfra du vil returnere værdien.
• opslagsværdi henviser til den værdi, der skal matches, i betragtning af tilfælde af tegn, i opslag_array.
• opslag_array henviser til det celleområde, hvor du vil have MATCH at sammenligne med opslagsværdi.

For eksempel at vide JIMMYs eksamensresultat, brug følgende formel:

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast, undtagen i Excel 365 og Excel 2021.

INDEX og MATCH for at finde det nærmeste match

I Excel kan du støde på situationer, hvor du skal finde den nærmeste eller nærmeste match til en bestemt værdi i et datasæt. I sådanne scenarier kan det være utrolig nyttigt at bruge en kombination af INDEX- og MATCH-funktioner sammen med ABS- og MIN-funktionerne.

• matrix refererer til det område, hvorfra du vil returnere værdien.
• opslag_array refererer til det område af værdier, hvor du vil finde det tætteste match til opslagsværdi.
• opslagsværdi henviser til værdien for at finde dens nærmeste match.

For eksempel for at finde ud af det hvis score er tættest på 85, brug følgende formel til søg efter den nærmeste score på 85 i C2:C11 , hent den tilsvarende værdi fra A2:A11.

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast, undtagen i Excel 365 og Excel 2021.

Fantastiske! Brug af effektive faner i Excel som Chrome, Edge, Firefox og Safari!
Spar 50% af din tid, og reducer tusindvis af museklik for dig hver dag!

INDEX og MATCH for at anvende et opslag med flere kriterier

For at finde en værdi, der opfylder flere betingelser, der kræver, at du søger på tværs af to eller flere kolonner, skal du bruge følgende formel. Formlen giver dig mulighed for at udføre et multikriterieopslag ved at specificere forskellige betingelser på tværs af forskellige kolonner, hvilket hjælper dig med at finde den ønskede værdi, der opfylder alle de angivne kriterier.

√ Bemærk: Dette er en matrixformel, der kræver, at du indtaster med Ctrl + Flytte + Indtast. Et par krøllede parenteser vises derefter i formellinjen.

• matrix refererer til det område, hvorfra du vil returnere værdien.
• (lookup_value=lookup_array) repræsenterer en enkelt betingelse. Denne betingelse kontrollerer, om en bestemt opslagsværdi matcher værdierne i opslag_array.

For eksempel at finde score af klasse A's Coco, hvis fødselsdato er 7/2/2008, kan du bruge følgende formel:

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

INDEX og MATCH for at anvende et opslag på tværs af flere kolonner

Forestil dig et scenarie, hvor du har at gøre med flere datakolonner. Den første kolonne fungerer som en nøgle til at klassificere dataene i de andre kolonner. For at bestemme kategorien eller klassifikationen for en specifik post, skal du udføre en søgning på tværs af datakolonnerne og knytte den til den relevante nøgle i referencekolonnen.

For eksempel, i tabellen nedenfor, hvordan kan vi matche eleven Shawn med hans tilsvarende klasse ved hjælp af INDEX og MATCH? Nå, du kan opnå det med en formel, men formlen er ret omfattende og kan være udfordrende at forstå, endsige huske og skrive.

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

Det er her Kutools til Excel's Indeks og match på flere kolonner funktion er praktisk. Det forenkler processen, hvilket gør det hurtigt og nemt at matche specifikke poster med deres tilsvarende kategorier. For at låse op for dette kraftfulde værktøj og nemt matche Shawn med sin klasse download og installer Kutools til Excel-tilføjelsen, og gør derefter følgende:

1. Vælg den destinationscelle, hvor du vil vise den matchende klasse.
2. På Kutools fanebladet, klik på Formelhjælper > Opslag og reference > Indeks og match på flere kolonner.
3. 
3. Gør som følger i pop op-dialogboksen:
   1. Klik på 1 knappen ved siden af Opslag_col for at vælge den kolonne, der indeholder de nøgleoplysninger, du vil returnere, dvs. klassenavnene. (Du kan kun vælge en enkelt kolonne her.)
   2. Klik på 2 knappen ved siden af Table_rng for at markere cellerne, så de matcher værdierne i den valgte Opslag_coldvs. elevernes navne.
   3. Klik på den 3 knappen ved siden af Opslagsværdi for at vælge den celle, der indeholder elevens navn, du vil matche med deres klasse, i dette tilfælde Shawn.
   4. Klik OK.
   5. 

Resultat

Kutools har automatisk genereret formlen, og du vil se Shawns klassenavn vist i destinationscellen med det samme.

INDEX og MATCH for at slå op efter den første ikke-blanke værdi

For at hente den første ikke-blanke værdi, ignorerer fejl, fra en kolonne eller en række, kan du bruge en formel baseret på INDEX og MATCH funktioner. Men hvis du ikke vil ignorere fejlene fra dit område, skal du tilføje ISBLANK-funktionen.

• Få den første ikke-tomme værdi i en kolonne eller række, der ignorerer fejl:

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• Få den første ikke-blanke værdi i en kolonne eller række inklusive fejl:

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

INDEX og MATCH for at søge efter den første numeriske værdi

For at hente den første numeriske værdi fra en kolonne eller en række skal du bruge formlen baseret på funktionerne INDEX, MATCH og ISNUMBER.

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

INDEX og MATCH for at søge efter MAX eller MIN associationer

Hvis du har brug for at hente en værdi forbundet med maksimum- eller minimumværdien inden for et område, kan du bruge MAX- eller MIN-funktionen sammen med INDEX- og MATCH-funktionerne.

• INDEX og MATCH for at hente en værdi forbundet med den maksimale værdi:
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• INDEX og MATCH for at hente en værdi forbundet med minimumsværdien:
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• Der er to argumenter i ovenstående formler:
• • matrix henviser til det område, hvorfra du vil returnere de relaterede oplysninger.
  • opslag_array repræsenterer det sæt af værdier, der skal undersøges eller søges efter specifikke kriterier, dvs. maksimum- eller minimumværdier.

For eksempel, hvis du vil bestemme hvem har den højeste score, anvender følgende formel:

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

Tip: Skræddersy dine egne #N/A fejlmeddelelser

Når du arbejder med Excels INDEX- og MATCH-funktioner, kan du støde på fejlen #N/A, når der ikke er noget matchende resultat. For eksempel, i tabellen nedenfor, når du forsøger at finde scoren for en elev ved navn Samantha, vises en #N/A fejl, da hun ikke er til stede i datasættet.

For at gøre dine regneark mere brugervenlige kan du tilpasse denne fejlmeddelelse ved at pakke din INDEX MATCH-formel ind i IFNA-funktionen:

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

Ovenfor er alt det relevante indhold relateret til INDEX- og MATCH-funktionerne i Excel. Jeg håber, du finder tutorialen nyttig. Hvis du ønsker at udforske flere Excel-tip og tricks, klik her for at få adgang til vores omfattende samling af over tusindvis af tutorials.