INDEX & MATCH Funksjoner kombinert i Excel (10 enkle eksempler)

Excel har mange funksjoner - omtrent 450+ av dem.

Og mange av disse er rett og slett fantastiske. Mengden arbeid du kan få gjort med noen få formler overrasker meg fortsatt (selv etter å ha brukt Excel i 10+ år).

Og blant alle disse fantastiske funksjonene skiller INDEX MATCH -funksjonskombinasjonen seg ut.

Jeg er en stor fan av INDEX MATCH combo, og jeg har gjort det ganske klart mange ganger.

Jeg skrev til og med en artikkel om Index Match mot VLOOKUP som utløste litt debatt (du kan sjekke kommentarfeltet for litt fyrverkeri).

Og i dag skriver jeg denne artikkelen utelukkende fokusert på Index Match for å vise deg noen enkle og avanserte scenarier der du kan bruke denne kraftige formelkombinasjonen og få jobben gjort.

Merk: Det er andre oppslagsformler i Excel - for eksempel VLOOKUP og HLOOKUP, og disse er flotte. Mange synes VLOOKUP er enklere å bruke (og det er også sant). Jeg tror INDEX MATCH er et bedre alternativ i mange tilfeller. Men siden folk synes det er vanskelig, blir det mindre brukt. Så jeg prøver å forenkle det ved hjelp av denne opplæringen.

Nå før jeg viser deg hvordan kombinasjonen av INDEX MATCH endrer verden for analytikere og datavitenskapere, la meg først introdusere deg for de enkelte delene - INDEX og MATCH -funksjoner.

INDEX-funksjon: Finner verdien basert på koordinater

Den enkleste måten å forstå hvordan indeksfunksjon fungerer, er å tenke på det som en GPS -satellitt.

Så snart du forteller satellitten breddegrad og lengdegradskoordinater, vil den vite nøyaktig hvor du skal dra og finne den plasseringen.

Så til tross for at det har et forbløffende antall lat-lange kombinasjoner, ville satellitten vite nøyaktig hvor du skal lete.

Jeg søkte raskt etter arbeidsstedet mitt, og dette er hva jeg fikk.

Uansett, nok av geografi.

Akkurat som en satellitt trenger bredde- og lengdegradskoordinater, ville INDEX -funksjonen i Excel trenge rad- og kolonnummeret for å vite hvilken celle du refererer til.

Og det er Excel INDEX-funksjonen i et nøtteskall.

Så la meg definere det i enkle ord for deg.

INDEX -funksjonen vil bruke radnummeret og kolonnenummeret til å finne en celle i det angitte området og returnere verdien i den.

I seg selv er INDEX en veldig enkel funksjon, uten verktøy. Tross alt, i de fleste tilfeller er det ikke sannsynlig at du kjenner rad- og kolonnetallene.

Men…

Det at du kan bruke den med andre funksjoner (hint: MATCH) som kan finne radnummeret og kolonnetallet, gjør INDEX til en ekstremt kraftig Excel -funksjon.

Nedenfor er syntaksen til INDEX -funksjonen:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

• matrise - en rekkevidde av celler eller en matrisekonstant.
• radnummer - radnummeret som verdien skal hentes fra.
• [col_num] - kolonnetallet som verdien skal hentes fra. Selv om dette er et valgfritt argument, men hvis radnummer ikke er angitt, må det oppgis.
• [arealnummer] - (Valgfritt) Hvis array -argument består av flere områder, vil dette tallet bli brukt til å velge referansen fra alle områdene.

INDEX -funksjonen har 2 syntakser (bare FYI).

Den første brukes i de fleste tilfeller. Den andre brukes bare i avanserte tilfeller (for eksempel å gjøre en tre-veis oppslag) som vi vil dekke i et av eksemplene senere i denne opplæringen.

Men hvis du er ny i denne funksjonen, bare husk den første syntaksen.

Nedenfor er en video som forklarer hvordan du bruker INDEX -funksjonen

MATCH -funksjon: Finner posisjonen på en oppslagsverdi

Når jeg går tilbake til mitt tidligere eksempel på lengdegrad og breddegrad, er MATCH funksjonen som kan finne disse posisjonene (i Excel -regnearkverdenen).

På et enkelt språk kan Excel MATCH -funksjonen finne posisjonen til en celle i et område.

Og på hvilket grunnlag ville den finne en celles posisjon?

Basert på oppslagsverdien.

For eksempel, hvis du har en liste som vist nedenfor og du vil finne posisjonen til navnet "Merk" i den, kan du bruke MATCH -funksjonen.

Funksjonen returnerer 3, da det er posisjonen til cellen med navnet Mark i.

MATCH -funksjonen begynner å lete opp og ned etter oppslagsverdien (som er ‘Merk’) i det angitte området (som er A1: A9 i dette eksemplet). Så snart den finner navnet, returnerer den posisjonen i det spesifikke området.

Nedenfor er syntaksen til MATCH -funksjonen i Excel.

= MATCH (oppslagsverdi, oppslag_array, [match_type])

• oppslag_verdi - Verdien du leter etter en kamp i lookup_array.
• oppslag_array - Celleområdet du søker etter oppslagsverdi.
• [match_type] - (Valgfritt) Dette spesifiserer hvordan excel skal se ut for en matchende verdi. Det kan ta tre verdier -1, 0 eller 1.

Forstå Match Type Argument i MATCH -funksjon

Det er en ekstra ting du trenger å vite om MATCH -funksjonen, og den handler om hvordan den går gjennom dataene og finner celleposisjonen.

Det tredje argumentet til MATCH -funksjonen kan være 0, 1 eller -1.

Nedenfor er en forklaring på hvordan disse argumentene fungerer:

• 0 - dette vil se etter en eksakt samsvar med verdien. Hvis det blir funnet en eksakt samsvar, returnerer MATCH -funksjonen celleposisjonen. Ellers vil det returnere en feil.
• 1 - dette finner den største verdien som er mindre enn eller lik oppslagsverdien. For at dette skal fungere, må dataområdet ditt sorteres i stigende rekkefølge.
• -1 - dette finner den minste verdien som er større enn eller lik oppslagsverdien. For at dette skal fungere, må dataområdet ditt sorteres i synkende rekkefølge.

Nedenfor er en video som forklarer hvordan du bruker MATCH -funksjonen (sammen med matchningstype -argumentet)

For å oppsummere og si det i enkle ord:

• INDEX trenger celleposisjonen (rad- og kolonnummer) og gir celleverdien.
• MATCH finner posisjonen ved å bruke en oppslagsverdi.

La oss kombinere dem for å lage et kraftverk (INDEX + MATCH)

Nå som du har en grunnleggende forståelse av hvordan INDEX og MATCH -funksjonene fungerer individuelt, la oss kombinere disse to og lære om alle de fantastiske tingene det kan gjøre.

For å forstå dette bedre, har jeg noen få eksempler som bruker INDEX MATCH -kombinasjonen.

Jeg starter med et enkelt eksempel og viser deg også noen avanserte brukstilfeller.

Klikk her for å laste ned eksempelfilen

Eksempel 1: Et enkelt oppslag ved hjelp av kombinasjon av INDEX MATCH

La oss gjøre et enkelt oppslag med INDEX/MATCH.

Nedenfor er en tabell der jeg har karakterene for ti studenter.

Fra denne tabellen vil jeg finne merkene for Jim.

Nedenfor er formelen som enkelt kan gjøre dette:

= INDEKS ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)

Hvis du tenker at dette enkelt kan gjøres ved hjelp av en VLOOKUP -funksjon, har du rett! Dette er ikke den beste bruken av INDEX MATCH awesomeness. Til tross for at jeg er fan av INDEX MATCH, er det litt vanskeligere enn VLOOKUP. Hvis du bare vil hente data fra en kolonne til høyre, anbefaler jeg at du bruker VLOOKUP.

Grunnen til at jeg har vist dette eksemplet, som også enkelt kan gjøres med VLOOKUP, er for å vise deg hvordan INDEX MATCH fungerer i en enkel setting.

La meg nå vise en fordel med INDEX MATCH.

Anta at du har de samme dataene, men i stedet for å ha dem i kolonner, har du dem i rader (som vist nedenfor).

Vet du hva, du kan fortsatt bruke kombinasjon av INDEX MATCH for å få Jims karakterer.

Nedenfor er formelen som gir deg resultatet:

= INDEKS ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))

Vær oppmerksom på at du må endre området og bytte rad/kolonne deler for at denne formelen også skal fungere for horisontale data.

Dette kan ikke gjøres med VLOOKUP, men du kan fortsatt gjøre dette enkelt med HLOOKUP.

INDEX MATCH -kombinasjonen kan enkelt håndtere horisontale så vel som vertikale data.

Klikk her for å laste ned eksempelfilen

Eksempel 2: Slå opp til venstre

Det er mer vanlig enn du tror.

Mange ganger kan det hende du må hente dataene fra en kolonne til venstre for kolonnen som har oppslagsverdien.

Noe som vist nedenfor:

For å finne ut Michaels salg, må du lete til venstre.

Hvis du tenker VLOOKUP, la meg stoppe din rett der.

VLOOKUP er ikke laget for å lete etter og hente verdiene til venstre.

Kan du fortsatt gjøre det ved å bruke VLOOKUP?

Ja det kan du!

Men det kan bli til en lang og stygg formel.

Så hvis du vil gjøre et oppslag og hente data fra kolonnene til venstre, er det bedre å bruke kombinasjonen INDEX MATCH.

Nedenfor er formelen som vil få Michaels salgsnummer:

= INDEKS ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)

Et annet poeng her for INDEX MATCH. VLOOKUP kan bare hente dataene fra kolonnene til høyre for kolonnen som har oppslagsverdien.

Eksempel 3: Toveis oppslag

Så langt har vi sett eksemplene der vi ønsket å hente dataene fra kolonnen ved siden av kolonnen som har oppslagsverdien.

Men i virkeligheten spenner dataene ofte gjennom flere kolonner.

INDEX MATCH kan enkelt håndtere et toveis oppslag.

Nedenfor er et datasett med elevens karakterer i tre forskjellige fag.

Hvis du raskt vil hente karakterene til en elev i alle tre fagene, kan du gjøre det med INDEX MATCH.

Formelen nedenfor gir deg karakterene for Jim for alle de tre emnene (kopier og lim inn i en celle og dra for å fylle andre celler eller kopiere og lime inn på andre celler).

= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))

La meg raskt også forklare denne formelen.

INDEX -formelen bruker B2: D11 som området.

Den første MATCH bruker navnet (Jim i celle F3) og henter posisjonen til den i navnekolonnen (A2: A11). Dette blir radnummeret som dataene må hentes fra.

Den andre MATCH -formelen bruker emnetavnet (i celle G2) for å få posisjonen til det spesifikke emnetavnet i B1: D1. For eksempel er matematikk 1, fysikk 2 og kjemi er 3.

Siden disse MATCH -posisjonene mates inn i INDEX -funksjonen, returnerer den poengsummen basert på studentnavnet og fagnavnet.

Denne formelen er dynamisk, noe som betyr at hvis du endrer elevenavnet eller fagnavnene, vil det fortsatt fungere og hente de riktige dataene.

En flott ting med å bruke INDEX/MATCH er at selv om du bytter navn på emnene, vil det fortsette å gi deg det riktige resultatet.

Eksempel 4: Oppslagsverdi fra flere kolonner/kriterier

Anta at du har et datasett som vist nedenfor, og du vil hente merkene for "Mark Long".

Siden dataene er i to kolonner, kan jeg ikke lete etter Mark og få dataene.

Hvis jeg gjør det på den måten, skal jeg få merkedataene for Mark Frost og ikke Mark Long (fordi MATCH -funksjonen gir meg resultatet for MARKET den oppfyller).

En måte å gjøre dette på er å lage en hjelperkolonne og kombinere navnene. Når du har hjelpekolonnen, kan du bruke VLOOKUP og få merkedataene.

Hvis du er interessert i å lære hvordan du gjør dette, kan du lese denne opplæringen om bruk av VLOOKUP med flere kriterier.

Men med INDEX/MATCH -kombinasjonen trenger du ikke en hjelperkolonne. Du kan opprette en formel som håndterer flere kriterier i selve formelen.

Formelen nedenfor gir resultatet.

= INDEKS ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))

La meg raskt forklare hva denne formelen gjør.

MATCH -delen av formelen kombinerer oppslagsverdien (Merk og lang) så vel som hele oppslagsoppsettet. Når $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 brukes som oppslagsmatrisen, kontrollerer den faktisk oppslagsverdien mot den kombinerte strengen med fornavn og etternavn (atskilt med rørsymbolet).

Dette sikrer at du får det riktige resultatet uten å bruke noen hjelpekolonner.

Du kan også gjøre denne typen oppslag (der det er flere kolonner/kriterier) med VLOOKUP, men du må bruke en hjelperkolonne. INDEX MATCH -kombinasjonen gjør det litt enkelt å gjøre dette uten hjelpekolonner.

Eksempel 5: Få verdier fra hele raden/kolonnen

I eksemplene ovenfor har vi brukt INDEX -funksjonen for å hente verdi fra en bestemt celle. Du oppgir rad- og kolonnummeret, og den returnerer verdien i den spesifikke cellen.

Men du kan gjøre mer.

Du kan også bruke INDEX -funksjonen til å hente verdiene fra en hel rad eller kolonne.

Og hvordan kan dette være nyttig spør du!

Anta at du vil vite den totale poengsummen til Jim i alle de tre fagene.

Du kan bruke INDEX -funksjonen til først å få alle merkene til Jim og deretter bruke SUM -funksjonen for å få en total.

La oss se hvordan du gjør dette.

Nedenfor har jeg poengsummen til alle elevene i tre fag.

Formelen nedenfor vil gi meg den totale poengsummen til Jim i alle de tre fagene.

= SUMME (INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))

La meg forklare hvordan denne formelen fungerer.

Trikset her er å bruke 0 som kolonnenummer.

Når du bruker 0 som kolonnenummer i INDEX -funksjonen, returnerer den alle radverdiene. På samme måte, hvis du bruker 0 som radnummeret, returnerer det alle verdiene i kolonnen.

Så delen nedenfor i formelen returnerer en rekke verdier - {97, 70, 73}

INDEKS ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)

Hvis du bare skriver inn denne formelen ovenfor i en celle i Excel og trykker enter, vil du se en #VERDI! feil. Dette er fordi det ikke returnerer en enkelt verdi, men en rekke verdier.

Men ikke bekymre deg, mengden verdier er fortsatt der. Du kan kontrollere dette ved å velge formelen og trykke på F9 -tasten. Det vil vise deg resultatet av formelen som i dette tilfellet er en matrise med tre verdier - {97, 70, 73}

Nå, hvis du pakker denne INDEX -formelen inn i SUM -funksjonen, vil den gi deg summen av alle merkene som er scoret av Jim.

Du kan også bruke det samme for å få de høyeste, laveste og gjennomsnittlige karakterene til Jim.

Akkurat som vi har gjort dette for en student, kan du også gjøre dette for et emne. For eksempel, hvis du vil ha gjennomsnittlig poengsum i et emne, kan du beholde radnummeret som 0 i INDEX -formelen, og det vil gi deg alle kolonneverdiene til det emnet.

Klikk her for å laste ned eksempelfilen

Eksempel 6: Finn elevens karakter (omtrentlig samsvarsteknikk)

Så langt har vi brukt MATCH -formelen for å få nøyaktig samsvar med oppslagsverdien.

Men du kan også bruke den til å gjøre en omtrentlig kamp.

Hva i helvete er omtrentlig match?

La meg forklare.

Når du leter etter ting som navn eller ID -er, leter du etter en eksakt samsvar. Men noen ganger må du kjenne rekkevidden der oppslagsverdiene ligger. Dette er vanligvis tilfellet med tall.

For eksempel kan du som klasselærer vite hvilken karakter hver elev har i et emne, og karakteren avgjøres basert på poengsummen.

Nedenfor er et eksempel, hvor jeg vil ha karakteren for alle elevene og karakteren avgjøres ut fra tabellen til høyre.

Så hvis en student får mindre enn 33, er karakteren F, og hvis han/hun får mindre enn 50, men mer enn 33, er det E, og så videre.

Nedenfor er formelen som vil gjøre dette.

= INDEKS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)

La meg forklare hvordan denne formelen fungerer.

I MATCH -funksjonen har vi brukt 1 som [match_type] argument. Dette argumentet vil returnere den største verdien som er mindre enn eller lik oppslagsverdien.

Dette betyr at MATCH -formelen går gjennom merkeområdet, og så snart den finner et merkeområde som er lik eller mindre enn oppslagsmerkets verdi, stopper den der og returnerer posisjonen.

Så hvis oppslagsmerkeverdien er 20, vil MATCH -funksjonen returnere 1, og hvis den er 85, vil den returnere 5.

Og INDEX -funksjonen bruker denne posisjonsverdien for å få karakteren.

VIKTIG: For at dette skal fungere, må dataene dine sorteres i stigende rekkefølge. Hvis det ikke er det, kan du få feil resultater.

Vær oppmerksom på at ovennevnte også kan gjøres ved å bruke VLOOKUP -formelen nedenfor:

= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, TRUE)

Men MATCH -funksjonen kan gå et skritt videre når det kommer til omtrentlig match.

Du kan også ha synkende data og bruke kombinasjon av INDEX MATCH for å finne resultatet. For eksempel, hvis jeg endrer rekkefølgen på karaktertabellen (som vist nedenfor), kan jeg fortsatt finne karakterene til elevene.

For å gjøre dette er alt jeg trenger å gjøre å endre [match_type] -argumentet til -1.

Nedenfor er formelen jeg har brukt:

= INDEKS ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)

VLOOKUP kan også gjøre en omtrentlig samsvar, men bare når data er sortert i stigende rekkefølge (men det fungerer ikke hvis dataene er sortert i synkende rekkefølge).

Eksempel 7: Søk etter store og små bokstaver

Så langt har alle oppslagene vi har gjort vært ufølsomme for store og små bokstaver.

Dette betyr at om oppslagsverdien var Jim eller JIM eller jim, spilte det ingen rolle. Du får det samme resultatet.

Men hva om du vil at oppslaget skal skille mellom store og små bokstaver.

Dette er vanligvis tilfelle når du har store datasett og mulighet for gjentakelse eller forskjellige navn/ID -er (med den eneste forskjellen)

Anta for eksempel at jeg har følgende datasett av studenter der det er to studenter med navnet Jim (den eneste forskjellen er at en er angitt som Jim og en annen som jim).

Vær oppmerksom på at det er to elever med samme navn - Jim (celle A2 og A5).

Siden et vanlig oppslag ikke ville fungere, må du gjøre et sakssensitivt oppslag.

Nedenfor er formelen som gir deg det riktige resultatet. Siden dette er en matriseformel, må du bruke Control + Shift + Enter.

= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)

La meg forklare hvordan denne formelen fungerer.

EKSAKT -funksjonen ser etter en nøyaktig samsvar med oppslagsverdien (som er ‘jim’ i dette tilfellet). Den går gjennom alle navnene og returnerer FALSK hvis den ikke er en treff og SANN hvis den er en treff.

Så utgangen til EXAKT -funksjonen i dette eksemplet er - {FALSE; FALSE; FALSE; TRUE; FALSE; FALSE; FALSE; FALSE; FALSE; FALSE}

Vær oppmerksom på at det bare er en TRUE, det er da EXAKT -funksjonen fant en perfekt match.

MATCH -funksjonen finner deretter posisjonen til TRUE i matrisen som returneres av EXACT -funksjonen, som er 4 i dette eksemplet.

Når vi har posisjonen, bruker INDEX -funksjonen den til å finne merkene.

Eksempel 8: Finn den nærmeste kampen

La oss komme litt avansert nå.

Anta at du har et datasett der du vil finne personen som har arbeidserfaringen nærmest den nødvendige erfaringen (nevnt i celle D2).

Selv om oppslagsformler ikke er laget for å gjøre dette, kan du kombinere det med andre funksjoner (for eksempel MIN og ABS) for å få dette gjort.

Nedenfor er formelen som vil finne personen med opplevelsen nærmest den nødvendige og returnere navnet på personen. Vær oppmerksom på at opplevelsen må være nærmest (som enten kan være mindre eller mer).

= INDEKS ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))

Siden dette er en matriseformel, må du bruke Control + Shift + Enter.

Trikset i denne formelen er å endre oppslagsverdien og oppslagsoppsettet for å finne den minste erfaringsforskjellen i nødvendige og faktiske verdier.

Før jeg forklarer formelen, la oss forstå hvordan du ville gjøre det manuelt.

Du vil gå gjennom hver celle i kolonne B og finne forskjellen i opplevelsen mellom det som kreves og det en person har. Når du har alle forskjellene, finner du den som er minst og henter navnet på den personen.

Dette er akkurat det vi gjør med denne formelen.

La meg forklare.

Oppslagsverdien i MATCH-formelen er MIN (ABS (D2-B2: B15)).

Denne delen gir deg minimumsforskjellen mellom den gitte erfaringen (som er 2,5 år) og alle de andre erfaringene. I dette eksemplet returnerer det 0,3

Legg merke til at jeg har brukt ABS for å sikre at jeg leter etter det nærmeste (som kan være mer eller mindre enn den gitte opplevelsen).

Nå blir denne minimumsverdien vår oppslagsverdi.

Oppslagsoppsettet i MATCH-funksjonen er ABS (D2- $ B $ 2: $ B $ 15).

Dette gir oss en rekke tall hvorfra 2,5 (den nødvendige erfaringen) er trukket fra.

Så nå har vi en oppslagsverdi (0,3) og en oppslagsoppstilling ({6.8; 0,8; 19,5; 21,8; 14,5; 11,2; 0,3; 9,2; 2; 9,8; 14,8; 0,4; 23,8; 2,9})

MATCH -funksjonen finner posisjonen 0,3 i denne matrisen, som også er posisjonen til personens navn som har den nærmeste opplevelsen.

Dette posisjonsnummeret brukes deretter av INDEX -funksjonen for å returnere navnet på personen.

Relatert Les: Finn den nærmeste kampen i Excel (eksempler ved hjelp av oppslagsformler)

Klikk her for å laste ned eksempelfilen

Eksempel 9: Bruk INDEX MATCH med jokertegn

Hvis du vil slå opp en verdi når det er et delvis samsvar, må du bruke jokertegn.

For eksempel er nedenfor et datasett med firmanavn og deres markedsverdi, og du vil ha markedsverdi. data for de tre selskapene til høyre.

Siden dette ikke er eksakte treff, kan du ikke gjøre et vanlig oppslag i dette tilfellet.

Men du kan fortsatt få de riktige dataene ved å bruke en stjerne (*), som er et jokertegn.

Nedenfor er formelen som gir deg dataene ved å matche firmanavnene fra hovedkolonnen og hente markedsverdien for den.

= INDEKS ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)

La meg forklare hvordan denne formelen fungerer.

Siden det ikke er noen eksakt samsvar med oppslagsverdier, har jeg brukt D2 & ”*” som oppslagsverdi i MATCH -funksjonen.

En stjerne er et jokertegn som representerer et hvilket som helst antall tegn. Dette betyr at Apple* i formelen vil bety enhver tekststreng som starter med ordet Apple og kan ha et hvilket som helst antall tegn etter det.

Så når Eple* brukes som oppslagsverdi og MATCH -formelen ser etter den i kolonne A, returnerer den posisjonen til ‘Apple Inc.’, slik den starter med ordet Apple.

Du kan også bruke jokertegn for å finne tekststrenger der oppslagsverdien er mellom. For eksempel, hvis du bruker * Apple * som oppslagsverdi, vil den finne en streng som har ordet apple hvor som helst i den.

Merk: Denne teknikken fungerer bra når du bare har én forekomst av matching. Men hvis du har flere forekomster av matching (for eksempel Apple Inc og Apple Corporation, vil MATCH -funksjonen bare returnere posisjonen til den første matchende forekomsten.

Eksempel 10: Treveis oppslag

Dette er en avansert bruk av INDEX MATCH, men jeg vil fortsatt dekke det for å vise deg kraften i denne kombinasjonen.

Husk at jeg sa at INDEX -funksjonen har to syntakser:

= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])

Så langt i alle våre eksempler har vi bare brukt det første.

Men for en tre-veis oppslag må du bruke den andre syntaksen.

La meg først forklare hva et treveis utseende betyr.

I et toveis oppslag bruker vi INDEX MATCH-formelen for å få karakterene når vi har elevens navn og emnetavnet. For eksempel, å hente karakterene til Jim i matematikk er et toveis oppslag.

Et treveis utseende ville gi en annen dimensjon til den. Anta for eksempel at du har et datasett som vist nedenfor, og du vil vite poengsummen til Jim i matematikk ved midtveiseksamen, da ville dette være treveis oppslag.

Nedenfor er formelen som vil gi resultatet.

= INDEX (($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23), MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0), MATCH (G $ 4 , $ B $ 2: $ D $ 2,0), (IF (G $ 3 = "Unit Test", 1, IF (G $ 3 = "Mid Term", 2,3))))

Formelen ovenfor sjekket for tre ting - navnet på studenten, emnet og eksamen. Etter at den finner den riktige verdien, returnerer den den i cellen.

La meg forklare hvordan denne formelen fungerer ved å bryte ned formelen i deler.

• array - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): I stedet for å bruke en enkelt matrise, i dette tilfellet, har jeg brukt tre matriser i parentes.
• row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH -funksjonen brukes til å finne posisjonen til elevens navn i celle $ F $ 5 i listen over elevens navn.
• col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): MATCH -funksjonen brukes til å finne plasseringen til emnets navn i celle $ B $ 2 i listen over emnets navn.
• [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): Verdien for områdenummer forteller INDEX -funksjonen hvilken av de tre matrisene som skal brukes for å hente verdien. Hvis undersøkelsen er Unit Term, vil IF -funksjonen returnere 1 og INDEX -funksjonen vil bruke den første matrisen for å hente verdien. Hvis eksamen er midtveis, vil IF-formelen returnere 2, ellers vil den returnere 3.

Dette er et avansert eksempel på bruk av INDEX MATCH, og du vil neppe finne en situasjon når du må bruke dette. Men det er fortsatt godt å vite hva Excel -formler kan gjøre.

Klikk her for å laste ned eksempelfilen

Hvorfor er INDEX/MATCH bedre enn VLOOKUP?

Eller er det?

Ja, det er det - i de fleste tilfeller.

Jeg kommer med saken min om en stund.

Men før jeg gjør det, la meg si dette - VLOOKUP er en ekstremt nyttig funksjon, og jeg elsker den. Det kan gjøre mange ting i Excel, og jeg bruker det nå og da selv. Når det er sagt, betyr det ikke at det ikke kan være noe bedre, og INDEX/MATCH (med mer fleksibilitet og funksjonalitet) er bedre.

Så hvis du vil gjøre noen grunnleggende oppslag, er det bedre å bruke VLOOKUP.

INDEX/MATCH er VLOOKUP på steroider. Og når du først har lært INDEX/MATCH, foretrekker du kanskje alltid å bruke den (spesielt på grunn av fleksibiliteten den har).

Uten å strekke det for langt, la meg raskt gi deg årsakene til at INDEX/MATCH er bedre enn VLOOKUP.

INDEX/MATCH kan se til venstre (så vel som til høyre) for oppslagsverdien

Jeg dekket det i et av eksemplene ovenfor.

Hvis du har en verdi til venstre for oppslagsverdien, kan du ikke gjøre det med VLOOKUP

I hvert fall ikke med bare VLOOKUP.

Ja, du kan kombinere VLOOKUP med andre formler og få det gjort, men det blir komplisert og rotete.

INDEX/MATCH, derimot, er laget for å slå opp overalt (det være seg venstre, høyre, opp eller ned)

INDEX/MATCH kan arbeide med vertikale og horisontale områder

Igjen, med full respekt for VLOOKUP, er det ikke laget for å gjøre dette.

Tross alt står V i VLOOKUP for vertikal.

VLOOKUP kan bare gå gjennom data som er vertikale, mens INDEX/MATCH kan gå gjennom data både vertikalt og horisontalt.

Selvfølgelig er det HLOOKUP -funksjonen for å ta vare på horisontalt oppslag, men det er ikke VLOOKUP da … ikke sant?

Jeg liker det faktum at INDEX MATCH -kombinasjonen er fleksibel nok til å fungere med både vertikale og horisontale data.

VLOOKUP kan ikke fungere med synkende data

Når det gjelder den omtrentlige kampen, er VLOOKUP og INDEX/MATCH på samme nivå.

Men INDEX MATCH tar poenget, da det også kan håndtere data som er i synkende rekkefølge.

Jeg viser dette i et av eksemplene i denne opplæringen der vi må finne karakteren til elevene basert på karaktertabellen. Hvis tabellen er sortert i synkende rekkefølge, ville VLOOKUP ikke fungere (men INDEX MATCH ville).

INDEX/MATCH kan være litt raskere

Jeg vil være sannferdig. Jeg kjørte ikke denne testen selv.

Jeg stoler på visdommen en Excel -mester - Charley Kyd.

Forskjellen i hastighet i VLOOKUP og INDEX/MATCH er neppe merkbar når du har små datasett. Men hvis du har tusenvis av rader og mange kolonner, kan dette være en avgjørende faktor.

I sin artikkel sier Charley Kyd:

“På sitt verste er INDEX-MATCH-metoden omtrent like rask som VLOOKUP; på sitt beste er det mye raskere. ”

INDEX/MATCH er uavhengig av den faktiske kolonneposisjonen

Hvis du har et datasett som vist nedenfor mens du henter poengsummen til Jim i fysikk, kan du gjøre det ved å bruke VLOOKUP.

Og for å gjøre det, kan du angi kolonnenummeret som 3 i VLOOKUP.

Alt er bra.

Men hva om jeg sletter matematikk -kolonnen.

I så fall vil VLOOKUP -formelen brytes.

Hvorfor? - Fordi det var hardkodet å bruke den tredje kolonnen, og når jeg sletter en kolonne mellom, blir den tredje kolonnen den andre kolonnen.

Å bruke INDEX/MATCH, i dette tilfellet, er bedre, siden du kan gjøre kolonnetallet dynamisk ved å bruke MATCH. Så i stedet for et kolonnenummer, sjekker det etter emnets navn og bruker det til å returnere kolonnenummeret.

Du kan sikkert gjøre det ved å kombinere VLOOKUP med MATCH, men hvis du kombinerer likevel, hvorfor ikke gjøre det med INDEX som er mye mer fleksibel.

Når du bruker INDEX/MATCH, kan du trygt sette inn/slette kolonner i datasettet ditt.

Til tross for alle disse faktorene, er det en grunn til at VLOOKUP er så populært.

Og det er en stor grunn.

VLOOKUP er enklere å bruke

VLOOKUP tar bare maksimalt fire argumenter. Hvis du kan pakke hodet rundt disse fire, er du i gang.

Og siden de fleste grunnleggende oppslagssakene også blir håndtert av VLOOKUP, har det raskt blitt den mest populære Excel -funksjonen.

Jeg kaller det King of Excel -funksjonene.

INDEX/MATCH er derimot litt vanskeligere å bruke. Du kan få et tak hvis du begynner å bruke den, men for en nybegynner er VLOOKUP langt lettere å forklare og lære.

Og dette er ikke et nullsumspill.

Så hvis du er ny i oppslagsverdenen og ikke vet hvordan du bruker VLOOKUP, er det bedre å lære det.

Jeg har en detaljert guide for bruk av VLOOKUP i Excel (med mange eksempler)

Min intensjon med denne artikkelen er ikke å sette to fantastiske funksjoner mot hverandre. Jeg ønsket å vise deg kraften i kombinasjonen INDEX MATCH og alle de flotte tingene den kan gjøre.

Håper du fant denne artikkelen nyttig.

Gi meg beskjed om tankene dine i kommentarfeltet, og gi meg beskjed hvis du finner feil i denne opplæringen.