Hvorfor INDEX og MATCH er bedre enn VLOOKUP i Excel

Vi har tidligere forklart nybegynnere hvordan man bruker de grunnleggende funksjonene til VLOOKUP (engelsk VLOOKUP, forkortelsen står for "vertical lookup function"). Og erfarne brukere ble vist flere mer kompliserte formler.

Og i denne artikkelen vil vi prøve å gi informasjon om en annen metode for å jobbe med vertikalt søk.

Du lurer kanskje på: "Hvorfor er dette nødvendig?". Og dette er nødvendig for å vise alle mulige søkemetoder. I tillegg hindrer mange VLOOKUP-begrensninger ofte å oppnå ønsket resultat. I denne forbindelse er INDEX( ) MATCH( ) mye mer funksjonell og mangfoldig, og de har også færre begrensninger.

Grunnleggende INDEKS MATCH

Siden hensikten med denne veiledningen er å vise hvor god denne funksjonen er, har vi La oss se på grunnleggende informasjon om prinsippene for driften. Og vi vil vise eksempler, og også vurdere hvorfor, det er bedre enn VLOOKUP ().

INDEX Funksjon Syntaks og bruk

Denne funksjonen hjelper til med å finne ønsket verdi blant de angitte søkeområdene basert på kolonne- eller linjenummeret. Syntaks:

=INDEKS(matrise, radnummer, kolonnenummer):

• array – området der søket skal finne sted;
• linjenummer – nummeret på linjen som skal søkes i den angitte matrisen. Hvis radnummeret er ukjent, må kolonnenummeret angis;
• kolonnenummer – nummeret på kolonnen som skal finnes i den angitte matrisen. Hvis verdien er ukjent, kreves et linjenummer.

Et eksempel på en enkel formel:

=INDEKS(A1:S10,2,3;XNUMX;XNUMX)

Funksjonen vil søke i området fra A1 til C10. Tallene viser hvilken rad (2) og kolonne (3) det skal vises ønsket verdi fra. Resultatet blir celle C2.

Ganske enkelt, ikke sant? Men når du jobber med ekte dokumenter, er det lite sannsynlig at du har informasjon om kolonnenummer eller celler. Det er det MATCH()-funksjonen er til for.

MATCH-funksjonens syntaks og bruk

MATCH()-funksjonen søker etter ønsket verdi og viser dets omtrentlige tall i det angitte søkeområdet.

Searchpos()-syntaksen ser slik ut:

=MATCH(verdi til oppslag, matrise til oppslag, samsvarstype)

• søkeverdi – nummeret eller teksten som skal finnes;
• søkt array – området der søket skal finne sted;
• samsvarstype – spesifiserer om du skal se etter den eksakte verdien eller verdiene nærmest den:
  • 1 (eller ingen verdi spesifisert) – returnerer den største verdien som er lik eller mindre enn verdien som ble spesifisert;
  • 0 – viser et eksakt samsvar med den søkte verdien. I kombinasjonen INDEX() MATCH() vil du nesten alltid trenge et eksakt samsvar, så vi skriver 0;
  • -1 – Viser den minste verdien som er større enn eller lik verdien angitt i formelen. Sortering utføres i synkende rekkefølge.

For eksempel, i området B1:B3 er New York, Paris, London registrert. Formelen nedenfor vil vise tallet 3 fordi London er tredje på listen:

=EXPOSE(London,B1:B3,0)

Hvordan jobbe med INDEX MATCH-funksjonen 

Du har sannsynligvis allerede begynt å forstå prinsippet som det felles arbeidet med disse funksjonene er bygget opp etter. Kort sagt, altså INDEX() søker etter ønsket verdi blant de angitte radene og kolonnene. Og MATCH() viser tallene til disse verdiene:

=INDEKS(kolonne som verdien returneres fra, MATCH(verdi å søke, kolonne å søke i, 0))

Har du fortsatt vanskelig for å forstå hvordan det fungerer? Kanskje et eksempel vil forklare bedre. Anta at du har en liste over verdens hovedsteder og deres befolkning:

For å finne ut størrelsen på befolkningen i en bestemt hovedstad, for eksempel hovedstaden i Japan, bruker vi følgende formel:

=INDEKS(C2:C10; MATCH(Japan; A2:A10,0;XNUMX))

Forklaring:

• MATCH()-funksjonen ser etter verdien – “Japan” i matrisen A2:A10 og returnerer tallet 3, fordi Japan er den tredje verdien i listen. 
• Dette tallet går tillinjenummer” i INDEX()-formelen og ber funksjonen skrive ut en verdi fra denne raden.

Så formelen ovenfor blir standardformelen INDEKS(C2:C10,3;XNUMX). Formelen søker fra cellene C2 til C10 og returnerer data fra den tredje cellen i dette området, det vil si C4, fordi nedtellingen starter fra den andre raden.

Vil du ikke skrive navnet på byen i formelen? Skriv det så i en hvilken som helst celle, si F1, og bruk det som referanse i MATCH()-formelen. Og du ender opp med en dynamisk søkeformel:

=ИНДЕКС(С2:С10, ПОИСКПОЗ( )(F1,A2:A10,0))

Viktig! Antall linjer inn matrise INDEX() må være det samme som antall rader i betraktet array i MATCH(), ellers får du feil resultat.

Vent litt, hvorfor ikke bare bruke VLOOKUP()-formelen?

=SØK OPP(F1; A2:C10; 3; Usann)

 Hva er vitsen med å kaste bort tid på å prøve å finne ut av alle disse kompleksitetene til INDEX MATCH?

I dette tilfellet spiller det ingen rolle hvilken funksjon du skal bruke. Dette er bare et eksempel for å forstå hvordan funksjonene INDEX() og MATCH() fungerer sammen. Andre eksempler vil vise hva disse funksjonene er i stand til i situasjoner der VLOOKUP er strømløs. 

INDEX MATCH eller VLOOKUP

Når de skal bestemme hvilken søkeformel som skal brukes, er mange enige om at INDEX() og MATCH() er langt bedre enn VLOOKUP. Imidlertid bruker mange fortsatt VLOOKUP(). For det første er VLOOKUP() enklere, og for det andre forstår ikke brukerne fullt ut alle fordelene ved å jobbe med INDEX() og MATCH(). Uten denne kunnskapen vil ingen gå med på å bruke tiden sin på å studere et komplekst system.

Her er de viktigste fordelene med INDEX() og MATCH() fremfor VLOOKUP():

• Søk fra høyre til venstre. VLOOKUP() kan ikke søke fra høyre til venstre, så verdiene du leter etter må alltid være i kolonnene lengst til venstre i tabellen. Men INDEX() og MATCH() kan håndtere dette uten problem. Denne artikkelen vil fortelle deg hvordan det ser ut i praksis: hvordan du finner ønsket verdi på venstre side.

1. Sikker tilsetning eller fjerning av kolonner. VLOOKUP()-formelen viser feil resultater når du fjerner eller legger til kolonner fordi VLOOKUP() trenger det nøyaktige kolonnenummeret for å lykkes. Når kolonner legges til eller fjernes, endres naturligvis også tallene deres. 

Og i formlene INDEX() og MATCH() spesifiseres en rekke kolonner, ikke individuelle kolonner. Som et resultat kan du trygt legge til og fjerne kolonner uten å måtte oppdatere formelen hver gang.

1. Ingen begrensninger på søkevolum. Når du bruker VLOOKUP(), må det totale antallet søkekriterier ikke overstige 255 tegn, ellers vil du få en #VERDI! Så hvis dataene dine inneholder et stort antall tegn, er INDEX() og MATCH() det beste alternativet.
2. Høy prosesseringshastighet. Hvis bordene dine er relativt små, vil du neppe merke noen forskjell. Men hvis tabellen inneholder hundrevis eller tusenvis av rader, og det følgelig er hundrevis og tusenvis av formler, vil INDEX () og MATCH () klare seg mye raskere enn VLOOKUP (). Faktum er at Excel vil behandle bare kolonnene som er spesifisert i formelen, i stedet for å behandle hele tabellen. 

Ytelsespåvirkningen av VLOOKUP() vil være spesielt merkbar hvis regnearket ditt inneholder et stort antall formler som VLOOKUP() og SUM(). Separate kontroller av VLOOKUP()-funksjonene kreves for å analysere hver verdi i en matrise. Så Excel må behandle en enorm mengde informasjon, og dette bremser arbeidet betydelig.

Formeleksempler 

Vi har allerede funnet ut nytten av disse funksjonene, så vi kan gå videre til den mest interessante delen: anvendelse av kunnskap i praksis.

Formel for å søke fra høyre til venstre

Som allerede nevnt kan ikke VLOOKUP utføre denne formen for søk. Så hvis de ønskede verdiene ikke er i kolonnen lengst til venstre, vil ikke VLOOKUP() gi et resultat. Funksjonene INDEX() og MATCH() er mer allsidige, og plasseringen av verdiene spiller ingen stor rolle for at de skal fungere.

For eksempel vil vi legge til en rangeringskolonne til venstre i tabellen vår og prøve å finne ut hvilken rangering når det gjelder innbyggertall hovedstaden i vårt land har.

I celle G1 skriver vi verdien som skal finnes, og bruker deretter følgende formel for å søke i området C1:C10 og returnere den tilsvarende verdien fra A2:A10:

=ИНДЕКС(А2:А10, ПОИСКПОЗ(G1,C1:C10,0))

Tekst. Hvis du planlegger å bruke denne formelen for flere celler, sørg for at du fikser områdene ved hjelp av absolutt adressering (f.eks. $A$2: $A$10 og $C$2: 4C$10).

INDEKS MER EKSPONERT MER EKSPONERT  for å søke i kolonner og rader

I eksemplene ovenfor har vi brukt disse funksjonene som en erstatning for VLOOKUP() for å returnere verdier fra et forhåndsdefinert rekkevidde. Men hva om du trenger å gjøre en matrise eller tosidig søk?

Det høres komplisert ut, men formelen for slike beregninger ligner standard INDEX() MATCH()-formelen, med bare én forskjell: MATCH()-formelen må brukes to ganger. Første gang for å få radnummeret, og andre gang for å få kolonnenummeret:

=INDEKS(matrise, MATCH(vertikal søkeverdi, søkekolonne, 0), MATCH(horisontal søkeverdi, søkerad, 0))

La oss se på tabellen nedenfor og prøve å lage en formel INDEX() EXPRESS() EXPRESS() for å vise demografi i et bestemt land for et valgt år.

Mållandet er i celle G1 (vertikalt oppslag) og målåret er i celle G2 (horisontalt oppslag). Formelen vil se slik ut:

=ИНДЕКС(B2:D11, ПОИСКПОЗ(G1,A2:A11,0), ПОИСКПОЗ(G2,B1:D1,0))

Hvordan denne formelen fungerer

Som med alle andre komplekse formler, er de lettere å forstå ved å dele dem ned i individuelle ligninger. Og så kan du forstå hva hver enkelt funksjon gjør:

• MATCH(G1;A2:A11,0;XNUMX) – ser etter en verdi (G1) i området A2:A11 og viser nummeret på denne verdien, i vårt tilfelle er det 2;
• SØK(G2;B1:D1,0;XNUMX) – ser etter en verdi (G2) i området B1:D1. I dette tilfellet ble resultatet 3.

De funnet rad- og kolonnenumrene sendes til den tilsvarende verdien i INDEX()-formelen:

=INDEKS(B2:D11,2,3;XNUMX;XNUMX)

Som et resultat har vi en verdi som er i en celle i skjæringspunktet mellom 2 rader og 3 kolonner i området B2:D11. Og formelen viser ønsket verdi, som er i celle D3.

Søk etter flere forhold med INDEX og MATCH

Hvis du har lest vår guide til VLOOKUP(), har du sannsynligvis prøvd flere søkeformler. Men denne søkemetoden har en betydelig begrensning - behovet for å legge til en hjelpekolonne.

Men den gode nyheten er at Med INDEX() og MATCH() kan du søke etter flere forhold uten å måtte redigere eller endre regnearket.

Her er den generelle flerbetingelsessøkeformelen for INDEX() MATCH():

{=ИНДЕКС(диапазон поиска, ПОИСКПОЗ(1,условие1=диапазон1)*(условвие2=диапазон2),0))}

Notatet: denne formelen må brukes sammen med hurtigtasten CTRL+SHIFT+ENTER.

Anta at du må finne verdien du leter etter basert på to forhold: Kjøper и Produkt.

Dette krever følgende formel:

=ИНДЕКС(С2:С10, ПОИСКПОЗ(1,(F1=A2:A10)*(F2=B1:B10),0))

I denne formelen er C2:C10 området der søket vil finne sted, F1 – denne tilstanden, A2:A10 — er området for å sammenligne tilstanden, F2 – tilstand 2, V2: V10 – område for sammenligning av tilstand 2.

Ikke glem å trykke på kombinasjonen på slutten av arbeidet med formelen CTRL + SKIFT + ENTER – Excel vil automatisk lukke formelen med krøllete klammeparenteser, som vist i eksempelet:

Hvis du ikke vil bruke en matriseformel for arbeidet ditt, legg til en annen INDEX() til formelen og trykk ENTER, det vil se ut som i eksemplet:

Hvordan disse formlene fungerer

Denne formelen fungerer på samme måte som standard INDEX() MATCH()-formel. For å søke etter flere forhold, oppretter du ganske enkelt flere falske og sanne forhold som representerer de riktige og ukorrekte individuelle betingelsene. Og så gjelder disse betingelsene for alle tilsvarende elementer i matrisen. Formelen konverterer False og True-argumentene til henholdsvis 0 og 1, og sender ut en matrise der 1 er de samsvarende verdiene som ble funnet i strengen. MATCH() vil finne den første verdien som samsvarer med 1 og sende den til INDEX()-formelen. Og det vil på sin side returnere den allerede ønskede verdien i den angitte linjen fra ønsket kolonne.

En formel uten en matrise avhenger av evnen til INDEX() til å håndtere dem på egen hånd. Den andre INDEX() i formelen samsvarer med falsk (0), så den sender hele matrisen med disse verdiene til MATCH()-formelen. 

Dette er en ganske lang forklaring på logikken bak denne formelen. For mer informasjon les artikkelen "INDEKSMATCH med flere betingelser'.

AVERAGE, MAX og MIN i INDEX og MATCH

Excel har sine egne spesialfunksjoner for å finne gjennomsnitt, maksimum og minimum. Men hva om du vil hente data fra cellen som er knyttet til disse verdiene? I dette tilfellet AVERAGE, MAX og MIN må brukes sammen med INDEX og MATCH.

INDEX MATCH og MAKS

For å finne den største verdien i kolonne D og vise den i kolonne C, bruk formelen: 

=ИНДЕКС(С2:С10, ПОИСКПОЗ(МАКС(D2:D10),D2:D10,0))

INDEX MATCH og MIN

For å finne den minste verdien i kolonne D og vise den i kolonne C, bruk følgende formel:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(МИН(D2:D10),D2:D10,0))

SØKEINDEKS og SERPENT

For å finne gjennomsnittsverdien i kolonne D og vise denne verdien i C:

=ИНДЕКС(С2:С10,ПОИСКПОЗ(СРЗНАЧ(D2:D10),D2:D10,-1))

Avhengig av hvordan dataene dine er skrevet, er det tredje argumentet til MATCH() enten 1, 0 eller -1:

• hvis kolonnene er sortert i stigende rekkefølge, sett 1 (da vil formelen beregne maksimumsverdien, som er mindre enn eller lik gjennomsnittsverdien);
• hvis sorteringen er synkende, så -1 (formelen vil gi ut minimumsverdien som er større enn eller lik gjennomsnittet);
• hvis oppslagsmatrisen inneholder en verdi som er nøyaktig lik gjennomsnittet, sett den til 0. 

 I vårt eksempel er populasjonen sortert i synkende rekkefølge, så vi setter -1. Og resultatet er Tokyo, siden befolkningsverdien (13,189 000) er nærmest gjennomsnittsverdien (XNUMX).

VLOOKUP() kan også utføre slike beregninger, men bare som en matriseformel: VLOOKUP med AVERAGE, MIN og MAX.

INDEX MATCH og ESND/IFERROR

Du har sannsynligvis allerede lagt merke til at hvis formelen ikke finner den ønskede verdien, gir den en feil # N / A. Du kan erstatte standard feilmelding med noe mer informativt. Sett for eksempel argumentet i formelen I det XNUMX:

=ЕСНД(ИНДЕКС(С2:С10,ПОИСКПОЗ(F1,A2:A10,0)),значение не найдено)

Med denne formelen, hvis du legger inn data som ikke er i tabellen, vil skjemaet gi deg den angitte meldingen.

Hvis du vil fange opp alle feilene, så unntatt I det XNUMX kan benyttes FEIL:

=FEIL(INDEKS(C2:C10,MATCH(F1;A2:A10,0)), "Noe gikk galt!")

Men husk at maskering av feil på denne måten ikke er en god idé, fordi standardfeil rapporterer brudd i formelen.

Vi håper du syntes guiden vår for bruk av INDEX MATCH()-funksjonen var nyttig.