Hvorfor INDEX og MATCH er bedre end VLOOKUP i Excel

Vi har tidligere forklaret begyndere, hvordan man bruger de grundlæggende funktioner i VLOOKUP (engelsk VLOOKUP, forkortelsen står for "vertical lookup function"). Og erfarne brugere fik vist flere mere komplicerede formler.

Og i denne artikel vil vi forsøge at give oplysninger om en anden metode til at arbejde med vertikal søgning.

Du undrer dig måske: "Hvorfor er dette nødvendigt?". Og dette er nødvendigt for at vise alle mulige søgemetoder. Derudover forhindrer talrige VLOOKUP-begrænsninger ofte at opnå det ønskede resultat. I denne henseende er INDEX( ) MATCH( ) meget mere funktionel og forskelligartet, og de har også færre begrænsninger.

Grundlæggende INDEX MATCH

Da formålet med denne guide er at vise, hvor god denne funktion er, har vi Lad os se på de grundlæggende oplysninger om principperne for dens drift. Og vi vil vise eksempler, og også overveje hvorfor, det er bedre end VLOOKUP ().

INDEX Funktion Syntaks og brug

Denne funktion hjælper med at finde den ønskede værdi blandt de angivne søgeområder baseret på kolonne- eller linjenummeret. Syntaks:

=INDEKS(matrix, rækkenummer, kolonnenummer):

• array – det område, hvor søgningen vil finde sted;
• linjenummer – nummeret på den linje, der skal søges i i det angivne array. Hvis rækkenummeret er ukendt, skal kolonnenummeret angives;
• kolonnenummer – nummeret på den kolonne, der skal findes i det angivne array. Hvis værdien er ukendt, kræves et linjenummer.

Et eksempel på en simpel formel:

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

Funktionen vil søge i området fra A1 til C10. Tallene viser hvilken række (2) og kolonne (3) den ønskede værdi skal vises fra. Resultatet bliver celle C2.

Ret simpelt, ikke? Men når du arbejder med rigtige dokumenter, er det usandsynligt, at du har oplysninger om kolonnenumre eller celler. Det er det, MATCH()-funktionen er til.

MATCH Funktion Syntaks og brug

Funktionen MATCH() søger efter den ønskede værdi og viser dens omtrentlige tal i det angivne søgeområde.

Searchpos()-syntaksen ser sådan ud:

=MATCH(værdi til opslag, matrix til opslag, matchtype)

• søgeværdi – det tal eller den tekst, der skal findes;
• søgt array – det område, hvor søgningen vil finde sted;
• matchtype – angiver, om der skal søges efter den nøjagtige værdi eller de værdier, der er tættest på den:
  • 1 (eller ingen angivet værdi) – returnerer den største værdi, der er lig med eller mindre end den værdi, der blev angivet;
  • 0 – viser et nøjagtigt match med den søgte værdi. I kombinationen INDEX() MATCH() skal du næsten altid have et nøjagtigt match, så vi skriver 0;
  • -1 – Viser den mindste værdi, der er større end eller lig med værdien angivet i formlen. Sortering udføres i faldende rækkefølge.

For eksempel er New York, Paris, London i intervallet B1:B3 registreret. Formlen nedenfor viser tallet 3, fordi London er tredje på listen:

=EXPOSER(London,B1:B3,0)

Sådan arbejder du med INDEX MATCH-funktionen 

Du er sikkert allerede begyndt at forstå princippet, hvorved det fælles arbejde af disse funktioner er bygget op. Så kort sagt INDEX() søger efter den ønskede værdi blandt de angivne rækker og kolonner. Og MATCH() viser tallene for disse værdier:

=INDEKS(kolonne, hvorfra værdien returneres, MATCH(værdi at søge, kolonne til at søge i, 0))

Har du stadig svært ved at forstå, hvordan det fungerer? Måske vil et eksempel forklare bedre. Antag, at du har en liste over verdens hovedstæder og deres befolkning:

For at finde ud af størrelsen af ​​befolkningen i en bestemt hovedstad, for eksempel hovedstaden i Japan, bruger vi følgende formel:

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

Forklaring:

• MATCH()-funktionen leder efter værdien – “Japan” i arrayet A2:A10 og returnerer tallet 3, fordi Japan er den tredje værdi på listen. 
• Dette tal går tillinjenummer” i formlen INDEX() og fortæller funktionen at udskrive en værdi fra denne række.

Så ovenstående formel bliver standardformlen INDEKS(C2:C10,3;XNUMX). Formlen søger fra cellerne C2 til C10 og returnerer data fra den tredje celle i dette område, det vil sige C4, fordi nedtællingen starter fra den anden række.

Vil du ikke skrive navnet på byen i formlen? Skriv det derefter i en hvilken som helst celle, sig F1, og brug det som reference i MATCH()-formlen. Og du ender med en dynamisk søgeformel:

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

Vigtigt! Antal linjer i matrix INDEX() skal være det samme som antallet af rækker i betragtet array i MATCH(), ellers får du det forkerte resultat.

Vent et øjeblik, hvorfor ikke bare bruge VLOOKUP()-formlen?

=OPSLAG(F1; A2:C10; 3; Falsk)

 Hvad er meningen med at spilde tid på at finde ud af alle disse kompleksiteter ved INDEX MATCH?

I dette tilfælde er det lige meget, hvilken funktion der skal bruges. Dette er blot et eksempel for at forstå, hvordan funktionerne INDEX() og MATCH() arbejder sammen. Andre eksempler vil vise, hvad disse funktioner er i stand til i situationer, hvor VLOOKUP er magtesløs. 

INDEX MATCH eller VLOOKUP

Når de skal beslutte, hvilken søgeformel der skal bruges, er mange enige om, at INDEX() og MATCH() er langt bedre end VLOOKUP. Mange mennesker bruger dog stadig VLOOKUP(). For det første er VLOOKUP() enklere, og for det andet forstår brugerne ikke fuldt ud alle fordelene ved at arbejde med INDEX() og MATCH(). Uden denne viden vil ingen gå med til at bruge deres tid på at studere et komplekst system.

Her er de vigtigste fordele ved INDEX() og MATCH() i forhold til VLOOKUP():

• Søg fra højre mod venstre. VLOOKUP() kan ikke søge fra højre mod venstre, så de værdier, du leder efter, skal altid være i kolonnerne længst til venstre i tabellen. Men INDEX() og MATCH() kan håndtere dette uden problemer. Denne artikel vil fortælle dig, hvordan det ser ud i praksis: hvordan man finder den ønskede værdi på venstre side.

1. Sikker tilføjelse eller fjernelse af kolonner. VLOOKUP()-formlen viser forkerte resultater, når du fjerner eller tilføjer kolonner, fordi VLOOKUP() har brug for det nøjagtige kolonnenummer for at lykkes. Når kolonner tilføjes eller fjernes, ændres deres tal naturligvis også. 

Og i formlerne INDEX() og MATCH() er der angivet en række kolonner, ikke individuelle kolonner. Som et resultat kan du sikkert tilføje og fjerne kolonner uden at skulle opdatere formlen hver gang.

1. Ingen grænser for søgevolumen. Når du bruger VLOOKUP(), må det samlede antal søgekriterier ikke overstige 255 tegn, ellers får du en #VALUE! Så hvis dine data indeholder et stort antal tegn, er INDEX() og MATCH() den bedste mulighed.
2. Høj behandlingshastighed. Hvis dine borde er relativt små, så vil du næppe bemærke nogen forskel. Men hvis tabellen indeholder hundredvis eller tusindvis af rækker, og der følgelig er hundreder og tusinder af formler, vil INDEX () og MATCH () klare sig meget hurtigere end VLOOKUP (). Faktum er, at Excel kun vil behandle de kolonner, der er angivet i formlen, i stedet for at behandle hele tabellen. 

Ydeevnepåvirkningen af ​​VLOOKUP() vil især være mærkbar, hvis dit regneark indeholder et stort antal formler som VLOOKUP() og SUM(). Separate kontroller af VLOOKUP()-funktionerne er nødvendige for at parse hver værdi i et array. Så Excel skal behandle en enorm mængde information, og det bremser arbejdet markant.

Formeleksempler 

Vi har allerede fundet ud af nytten af ​​disse funktioner, så vi kan gå videre til den mest interessante del: anvendelsen af ​​viden i praksis.

Formel til at søge fra højre mod venstre

Som allerede nævnt kan VLOOKUP ikke udføre denne form for søgning. Så hvis de ønskede værdier ikke er i kolonnen længst til venstre, vil VLOOKUP() ikke give et resultat. Funktionerne INDEX() og MATCH() er mere alsidige, og placeringen af ​​værdierne spiller ikke nogen stor rolle for, at de fungerer.

For eksempel vil vi tilføje en rangkolonne til venstre i vores tabel og forsøge at finde ud af, hvilken rangering med hensyn til befolkning, vores lands hovedstad indtager.

I celle G1 skriver vi den værdi, der skal findes, og bruger derefter følgende formel til at søge i området C1:C10 og returnere den tilsvarende værdi fra A2:A10:

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

Hurtig. Hvis du planlægger at bruge denne formel til flere celler, skal du sørge for at rette intervallerne ved hjælp af absolut adressering (f.eks. $A$2: $A$10 og $C$2: 4C$10).

INDEKS MERE EKSPONET MERE EKSPONET  at søge i kolonner og rækker

I ovenstående eksempler har vi brugt disse funktioner som en erstatning for VLOOKUP() for at returnere værdier fra et foruddefineret rækkevidde. Men hvad nu hvis du skal lave en matrix eller tosidet søgning?

Det lyder kompliceret, men formlen for sådanne beregninger ligner standardformlen INDEX() MATCH() med kun én forskel: MATCH()-formlen skal bruges to gange. Første gang for at få rækkenummeret, og anden gang for at få kolonnenummeret:

=INDEKS(matrix, MATCH(vertikal søgeværdi, søgekolonne, 0), MATCH(vandret søgeværdi, søgerække, 0))

Lad os se på tabellen nedenfor og prøve at lave en formel INDEX() EXPRESS() EXPRESS() for at vise demografi i et specifikt land for et valgt år.

Mållandet er i celle G1 (lodret opslag), og målåret er i celle G2 (vandret opslag). Formlen vil se sådan ud:

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

Sådan fungerer denne formel

Som med alle andre komplekse formler er de lettere at forstå ved at opdele dem i individuelle ligninger. Og så kan du forstå, hvad hver enkelt funktion gør:

• MATCH(G1;A2:A11,0;XNUMX) – leder efter en værdi (G1) i området A2:A11 og viser tallet på denne værdi, i vores tilfælde er det 2;
• SØGNING(G2;B1:D1,0;XNUMX) – leder efter en værdi (G2) i området B1:D1. I dette tilfælde var resultatet 3.

De fundne række- og kolonnenumre sendes til den tilsvarende værdi i INDEX()-formlen:

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

Som et resultat har vi en værdi, der er i en celle i skæringspunktet mellem 2 rækker og 3 kolonner i området B2:D11. Og formlen viser den ønskede værdi, som er i celle D3.

Søg efter flere forhold med INDEX og MATCH

Hvis du har læst vores guide til VLOOKUP(), har du sikkert prøvet flere søgeformler. Men denne søgemetode har en væsentlig begrænsning - behovet for at tilføje en hjælpekolonne.

Men den gode nyhed er det Med INDEX() og MATCH() kan du søge efter flere betingelser uden at skulle redigere eller ændre dit regneark.

Her er den generelle søgeformel med flere betingelser for INDEX() MATCH():

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

Noten: denne formel skal bruges sammen med tastaturgenvejen CTRL+SHIFT+ENTER.

Antag, at du skal finde den værdi, du leder efter, baseret på 2 forhold: Køber и Produkt.

Dette kræver følgende formel:

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

I denne formel er C2:C10 det område, hvori søgningen finder sted, F1 – denne betingelse, A2:A10 — er området for at sammenligne tilstanden, F2 – betingelse 2, V2: V10 – område til sammenligning af tilstand 2.

Glem ikke at trykke på kombinationen i slutningen af ​​arbejdet med formlen CTRL + SKIFT + ENTER – Excel lukker automatisk formlen med krøllede klammeparenteser, som vist i eksemplet:

Hvis du ikke ønsker at bruge en matrixformel til dit arbejde, så tilføj endnu en INDEX() til formlen og tryk ENTER, det vil se ud som i eksemplet:

Hvordan disse formler fungerer

Denne formel fungerer på samme måde som standard INDEX() MATCH()-formlen. For at søge efter flere betingelser opretter du blot flere falske og sande betingelser, der repræsenterer de korrekte og ukorrekte individuelle betingelser. Og så gælder disse betingelser for alle tilsvarende elementer i arrayet. Formlen konverterer argumenterne False og True til henholdsvis 0 og 1 og udsender et array, hvor 1 er de matchende værdier, der blev fundet i strengen. MATCH() finder den første værdi, der matcher 1, og sender den til INDEX()-formlen. Og det vil til gengæld returnere den allerede ønskede værdi i den angivne linje fra den ønskede kolonne.

En formel uden et array afhænger af INDEX()'s evne til at håndtere dem på egen hånd. Den anden INDEX() i formlen matcher falsk (0), så den sender hele arrayet med disse værdier til MATCH()-formlen. 

Dette er en ret lang forklaring på logikken bag denne formel. For mere information læs artiklen "INDEX MATCH med flere betingelser'.

AVERAGE, MAX og MIN i INDEX og MATCH

Excel har sine egne specielle funktioner til at finde gennemsnit, maksimum og minimum. Men hvad nu hvis du vil have data fra cellen, der er forbundet med disse værdier? I dette tilfælde AVERAGE, MAX og MIN skal bruges sammen med INDEX og MATCH.

INDEX MATCH og MAKS

For at finde den største værdi i kolonne D og vise den i kolonne C, skal du bruge formlen: 

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

INDEX MATCH og MIN

For at finde den mindste værdi i kolonne D og vise den i kolonne C, skal du bruge følgende formel:

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

SØGEINDEKS og SORGEN

Sådan finder du gennemsnitsværdien i kolonne D og viser denne værdi i C:

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

Afhængigt af hvordan dine data er skrevet, er det tredje argument til MATCH() enten 1, 0 eller -1:

• hvis kolonnerne er sorteret i stigende rækkefølge, sæt 1 (så vil formlen beregne den maksimale værdi, som er mindre end eller lig med gennemsnitsværdien);
• hvis sorteringen er faldende, så -1 (formlen vil udlæse minimumsværdien, der er større end eller lig med gennemsnittet);
• hvis opslagsarrayet indeholder en værdi, der er nøjagtigt lig med gennemsnittet, så sæt den til 0. 

 I vores eksempel er populationen sorteret i faldende rækkefølge, så vi sætter -1. Og resultatet er Tokyo, da befolkningsværdien (13,189) er tættest på gennemsnitsværdien (000).

VLOOKUP() kan også udføre sådanne beregninger, men kun som en matrixformel: VLOOKUP med AVERAGE, MIN og MAX.

INDEX MATCH og ESND/IFERROR

Du har sikkert allerede bemærket, at hvis formlen ikke kan finde den ønskede værdi, giver den en fejl # N / A. Du kan erstatte standardfejlmeddelelsen med noget mere informativt. Indstil for eksempel argumentet i formlen I det XNUMX:

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

Med denne formel, hvis du indtaster data, der ikke er i tabellen, vil formularen give dig den angivne besked.

Hvis du vil fange alle fejl, så undtagen I det XNUMX kan anvendes FEJL:

=FEJL(INDEKS(C2:C10,MATCH(F1;A2:A10,0)), "Noget gik galt!")

Men husk, at maskering af fejl på denne måde ikke er en god idé, fordi standardfejl rapporterer overtrædelser i formlen.

Vi håber, du fandt vores guide til brug af INDEX MATCH()-funktionen nyttig.