Excel har mange funktioner - omkring 450+ af dem.
Og mange af disse er simpelthen fantastiske. Mængden af arbejde, du kan få udført med et par formler, overrasker mig stadig (selv efter at have brugt Excel i 10+ år).
Og blandt alle disse fantastiske funktioner skiller INDEX MATCH -funktionskombinationen sig ud.
Jeg er en stor fan af INDEX MATCH combo, og jeg har gjort det ret klart mange gange.
Jeg skrev endda en artikel om Index Match Vs VLOOKUP, som udløste lidt debat (du kan tjekke kommentarfeltet for noget fyrværkeri).
Og i dag skriver jeg denne artikel udelukkende med fokus på Index Match for at vise dig nogle enkle og avancerede scenarier, hvor du kan bruge denne kraftfulde formelkombination og få arbejdet udført.
Bemærk: Der er andre opslagsformler i Excel - såsom VLOOKUP og HLOOKUP, og disse er gode. Mange mennesker synes, at VLOOKUP er lettere at bruge (og det er også rigtigt). Jeg tror, INDEX MATCH er en bedre mulighed i mange tilfælde. Men da folk synes det er svært, bliver det brugt mindre. Så jeg prøver at forenkle det ved hjælp af denne vejledning.Nu, før jeg viser dig, hvordan kombinationen af INDEX MATCH ændrer analytikernes og dataforskernes verden, lad mig først introducere dig til de enkelte dele - INDEX og MATCH -funktioner.
INDEX-funktion: Finder værdien baseret på koordinater
Den nemmeste måde at forstå, hvordan indeksfunktionen fungerer, er ved at tænke på det som en GPS -satellit.
Så snart du fortæller satellitten bredde- og længdegradskoordinaterne, ved den nøjagtigt, hvor den skal hen og finder den placering.
Så på trods af at det havde et forbløffende antal lat-lange kombinationer, ville satellitten vide nøjagtigt, hvor de skulle lede.
Jeg søgte hurtigt efter min arbejdsplads, og det var det, jeg fik.
Anyway, nok af geografi.
Ligesom en satellit har brug for bredde- og længdegradskoordinater, ville INDEX -funktionen i Excel have brug for række- og kolonnenummer for at vide, hvilken celle du henviser til.
Og det er Excel INDEX-funktion i en nøddeskal.
Så lad mig definere det i enkle ord for dig.
INDEX -funktionen vil bruge rækkenummer og kolonnenummer til at finde en celle i det givne område og returnere værdien i den.
I sig selv er INDEX en meget enkel funktion, uden brugbarhed. I de fleste tilfælde kender du trods alt ikke række- og kolonnenumrene.
Men…
Det faktum, at du kan bruge det med andre funktioner (hint: MATCH), der kan finde rækkenummeret og kolonnetallet, gør INDEX til en ekstremt kraftfuld Excel -funktion.
Nedenfor er syntaksen for INDEX -funktionen:
= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])
- array - -en område af celler eller en matrixkonstant.
- række_nummer - det rækkenummer, hvorfra værdien skal hentes.
- [col_num] - det kolonnenummer, hvorfra værdien skal hentes. Selvom dette er et valgfrit argument, men hvis row_num ikke er angivet, skal det angives.
- [områdenummer] - (Valgfrit) Hvis array -argument består af flere områder, vil dette nummer blive brugt til at vælge referencen fra alle områderne.
INDEX -funktionen har 2 syntakser (bare FYI).
Den første bruges i de fleste tilfælde. Den anden bruges kun i avancerede tilfælde (f.eks. Ved at lave et trevejsopslag), som vi vil dække i et af eksemplerne senere i denne vejledning.
Men hvis du er ny i denne funktion, skal du bare huske den første syntaks.
Nedenfor er en video, der forklarer, hvordan du bruger INDEX -funktionen
MATCH -funktion: Finder positionen på en opslagsværdi
Når jeg går tilbage til mit tidligere eksempel på længde- og breddegrad, er MATCH den funktion, der kan finde disse positioner (i Excel -regnearksverdenen).
I et enkelt sprog kan Excel MATCH -funktionen finde placeringen af en celle i et område.
Og på hvilket grundlag ville den finde en celles position?
Baseret på opslagsværdien.
For eksempel, hvis du har en liste som vist herunder, og du vil finde placeringen af navnet 'Mark' i den, kan du bruge MATCH -funktionen.
Funktionen returnerer 3, da det er placeringen af cellen med navnet Mark i.
MATCH -funktionen begynder at lede fra op til bund efter opslagsværdien (som er 'Mark') i det angivne område (som er A1: A9 i dette eksempel). Så snart det finder navnet, returnerer det positionen i det specifikke område.
Nedenfor er syntaksen for MATCH -funktionen i Excel.
= MATCH (opslagsværdi, opslag_array, [match_type])
- opslag_værdi - Den værdi, du leder efter en match i lookup_array.
- opslag_array - Celleområdet, hvor du søger efter opslagsværdien.
- [match_type] - (Valgfrit) Dette angiver, hvordan excel skal se ud for en matchende værdi. Det kan tage tre værdier -1, 0 eller 1.
Forstå matchtype -argument i MATCH -funktion
Der er en ekstra ting, du skal vide om MATCH -funktionen, og den handler om, hvordan den går igennem dataene og finder cellens position.
MATCH -funktionens tredje argument kan være 0, 1 eller -1.
Nedenfor er en forklaring på, hvordan disse argumenter fungerer:
- 0 - dette vil lede efter en nøjagtig matchning af værdien. Hvis der findes et nøjagtigt match, returnerer MATCH -funktionen cellens position. Ellers returnerer det en fejl.
- 1 - dette finder den største værdi, der er mindre end eller lig med opslagsværdien. For at dette skal fungere, skal dit dataområde sorteres i stigende rækkefølge.
- -1 - dette finder den mindste værdi, der er større end eller lig med opslagsværdien. For at dette kan fungere, skal dit dataområde sorteres i faldende rækkefølge.
Nedenfor er en video, der forklarer, hvordan du bruger funktionen MATCH (sammen med match type -argumentet)
For at opsummere og sige det i enkle ord:
- INDEX har brug for cellens position (række- og kolonnenummer) og giver celleværdien.
- MATCH finder positionen ved hjælp af en opslagsværdi.
Lad os kombinere dem for at oprette et kraftcenter (INDEX + MATCH)
Nu hvor du har en grundlæggende forståelse for, hvordan INDEX og MATCH -funktioner fungerer individuelt, lad os kombinere disse to og lære om alle de vidunderlige ting, det kan gøre.
For at forstå dette bedre har jeg et par eksempler, der bruger INDEX MATCH -kombinationen.
Jeg starter med et enkelt eksempel og viser dig også nogle avancerede anvendelsessager.
Klik her for at downloade eksempelfilen
Eksempel 1: Et simpelt opslag ved hjælp af INDEX MATCH Combo
Lad os lave et simpelt opslag med INDEX/MATCH.
Nedenfor er en tabel, hvor jeg har karaktererne for ti elever.
Fra denne tabel vil jeg finde mærkerne for Jim.
Nedenfor er formlen, der let kan gøre dette:
= INDEX ($ A $ 2: $ B $ 11, MATCH ("Jim", $ A $ 2: $ A $ 11,0), 2)
Hvis du nu tænker, at dette let kan gøres ved hjælp af en VLOOKUP -funktion, har du ret! Dette er ikke den bedste brug af INDEX MATCH awesomeness. På trods af at jeg er fan af INDEX MATCH, er det lidt sværere end VLOOKUP. Hvis det kun er at hente data fra en kolonne til højre, anbefaler jeg, at du bruger VLOOKUP.
Grunden til at jeg har vist dette eksempel, som også let kan gøres med VLOOKUP, er for at vise dig hvordan INDEX MATCH fungerer i en enkel indstilling.
Lad mig nu vise en fordel ved INDEX MATCH.
Antag, at du har de samme data, men i stedet for at have dem i kolonner, har du dem i rækker (som vist nedenfor).
Ved du hvad, du kan stadig bruge kombinationen INDEX MATCH til at få Jims karakterer.
Nedenfor er formlen, der giver dig resultatet:
= INDEX ($ B $ 1: $ K $ 2,2, MATCH ("Jim", $ B $ 1: $ K $ 1,0))
Bemærk, at du skal ændre rækkevidden og skifte række/kolonne dele for også at få denne formel til at fungere til vandrette data.
Dette kan ikke gøres med VLOOKUP, men du kan stadig gøre dette let med HLOOKUP.
INDEX MATCH kombinationen kan let håndtere vandrette såvel som lodrette data.Klik her for at downloade eksempelfilen
Eksempel 2: Opslag til venstre
Det er mere almindeligt, end du tror.
Mange gange kan det være nødvendigt at hente dataene fra en kolonne, der er til venstre for den kolonne, der har opslagsværdien.
Noget som vist herunder:
For at finde ud af Michaels salg skal du kigge til venstre.
Hvis du tænker VLOOKUP, lad mig stoppe din ret der.
VLOOKUP er ikke skabt til at lede efter og hente værdierne til venstre.
Kan du stadig gøre det ved hjælp af VLOOKUP?
Ja du kan!
Men det kan blive til en lang og grim formel.
Så hvis du vil lave et opslag og hente data fra kolonnerne til venstre, har du det bedre med at bruge INDEX MATCH combo.
Nedenfor er formlen, der får Michaels salgsnummer:
= INDEX ($ A $ 2: $ C $ 11, MATCH ("Michael", C2: C11,0), 2)
Eksempel 3: Tovejsopslag
Hidtil har vi set eksemplerne, hvor vi ønskede at hente dataene fra kolonnen ved siden af kolonnen, der har opslagsværdien.
Men i virkeligheden spænder dataene ofte gennem flere kolonner.
INDEX MATCH kan let håndtere et tovejsopslag.
Nedenfor er et datasæt med elevens karakterer i tre forskellige fag.
Hvis du hurtigt vil hente en elevs karakterer i alle tre fag, kan du gøre det med INDEX MATCH.
Nedenstående formel giver dig karaktererne for Jim for alle de tre emner (kopier og indsæt i en celle og træk for at udfylde andre celler eller kopier og indsæt på andre celler).
= INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 3, $ A $ 2: $ A $ 11,0), MATCH (G $ 2, $ B $ 1: $ D $ 1,0))
Lad mig hurtigt også forklare denne formel.
INDEX -formlen bruger B2: D11 som området.
Det første MATCH bruger navnet (Jim i celle F3) og henter placeringen af det i navnekolonnen (A2: A11). Dette bliver det rækkenummer, som dataene skal hentes fra.
Den anden MATCH -formel bruger emnets navn (i celle G2) til at få placeringen af det specifikke emnes navn i B1: D1. For eksempel er matematik 1, fysik 2 og kemi 3.
Da disse MATCH -positioner indføres i INDEX -funktionen, returnerer den scoren baseret på elevens navn og fagnavn.
Denne formel er dynamisk, hvilket betyder, at hvis du ændrer elevens navn eller emnenavne, vil det stadig fungere og hente de korrekte data.
En god ting ved at bruge INDEX/MATCH er, at selvom du udveksler navnene på emnerne, vil det fortsat give dig det korrekte resultat.Eksempel 4: Opslagsværdi fra flere kolonner/kriterier
Antag, at du har et datasæt som vist nedenfor, og du vil hente mærkerne for 'Mark Long'.
Da dataene er i to kolonner, kan jeg ikke søge efter Mark og få dataene.
Hvis jeg gør det på den måde, får jeg mærkesdataene for Mark Frost og ikke Mark Long (fordi MATCH -funktionen giver mig resultatet for MARKET, den opfylder).
En måde at gøre dette på er at oprette en hjælperkolonne og kombinere navnene. Når du har hjælpekolonnen, kan du bruge VLOOKUP og få karakterdataene.
Hvis du er interesseret i at lære at gøre dette, kan du læse denne vejledning om brug af VLOOKUP med flere kriterier.
Men med INDEX/MATCH -kombinationen behøver du ikke en hjælperkolonne. Du kan oprette en formel, der håndterer flere kriterier i selve formlen.
Nedenstående formel giver resultatet.
= INDEX ($ C $ 2: $ C $ 11, MATCH ($ E $ 3 & "|" & $ F $ 3, $ A $ 2: A11 & "|" & $ B $ 2: $ B $ 11,0))
Lad mig hurtigt forklare, hvad denne formel gør.
MATCH -delen af formlen kombinerer opslagsværdien (mærke og lang) samt hele opslagsgruppen. Når $ A $ 2: A11 & ”|” & $ B $ 2: $ B $ 11 bruges som opslagsmatrix, kontrollerer den faktisk opslagsværdien mod den kombinerede streng med for- og efternavn (adskilt af rørsymbolet).
Dette sikrer, at du får det rigtige resultat uden at bruge hjælpekolonner.
Du kan også foretage denne form for opslag (hvor der er flere kolonner/kriterier) med VLOOKUP, men du skal bruge en hjælperkolonne. INDEX MATCH combo gør det lidt let at gøre dette uden hjælpekolonner.Eksempel 5: Få værdier fra hele rækken/kolonnen
I eksemplerne ovenfor har vi brugt INDEX -funktionen til at hente værdi fra en bestemt celle. Du angiver række- og kolonnenummeret, og det returnerer værdien i den specifikke celle.
Men du kan mere.
Du kan også bruge INDEX -funktionen til at hente værdierne fra en hel række eller kolonne.
Og hvordan kan dette være nyttigt spørger du!
Antag, at du vil vide Jim's samlede score i alle de tre fag.
Du kan bruge INDEX -funktionen til først at få alle mærkerne fra Jim og derefter bruge SUM -funktionen til at få en total.
Lad os se, hvordan du gør dette.
Nedenfor har jeg scoringen af alle eleverne i tre fag.
Nedenstående formel giver mig den samlede score for Jim i alle de tre emner.
= SUM (INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0))
Lad mig forklare, hvordan denne formel fungerer.
Tricket her er at bruge 0 som kolonnenummer.
Når du bruger 0 som kolonnenummer i INDEX -funktionen, returnerer det alle rækkeværdierne. Tilsvarende, hvis du bruger 0 som rækkenummer, returnerer det alle værdierne i kolonnen.
Så nedenstående del af formlen returnerer en række værdier - {97, 70, 73}
INDEX ($ B $ 2: $ D $ 11, MATCH ($ F $ 4, $ A $ 2: $ A $ 11,0), 0)
Hvis du bare indtaster denne formel ovenfor i en celle i Excel og trykker på enter, vil du se en #VÆRDI! fejl. Dette skyldes, at det ikke returnerer en enkelt værdi, men en række værdier.
Men bare rolig, værdiansættet er der stadig. Du kan kontrollere dette ved at vælge formlen og trykke på F9 -tasten. Det viser dig resultatet af formlen, som i dette tilfælde er en matrix med tre værdier - {97, 70, 73}
Nu, hvis du pakker denne INDEX -formel ind i SUM -funktionen, giver den dig summen af alle de karakterer, Jim har scoret.
Du kan også bruge det samme til at få de højeste, laveste og gennemsnitlige karakterer af Jim.
Ligesom vi har gjort dette for en studerende, kan du også gøre dette for et emne. For eksempel, hvis du vil have den gennemsnitlige score i et emne, kan du beholde rækkenummeret som 0 i INDEX -formlen, og det vil give dig alle kolonneværdierne for det pågældende emne.
Klik her for at downloade eksempelfilen
Eksempel 6: Find elevens karakter (omtrentlig matchteknik)
Indtil videre har vi brugt MATCH -formlen til at få det nøjagtige match til opslagsværdien.
Men du kan også bruge den til at lave en omtrentlig match.
Hvad fanden er omtrentlig match?
Lad mig forklare.
Når du leder efter ting som navne eller id'er, leder du efter et eksakt match. Men nogle gange skal du kende det område, hvor dine opslagsværdier ligger. Dette er normalt tilfældet med tal.
For eksempel kan du som klasselærer vide, hvad karakteren er for hver elev i et emne, og karakteren afgøres ud fra score.
Nedenfor er et eksempel, hvor jeg vil have karakteren til alle eleverne og karakteren afgøres ud fra tabellen til højre.
Så hvis en elev får mindre end 33, er karakteren F, og hvis han/hun får mindre end 50, men mere end 33, er det E osv.
Nedenfor er formlen, der vil gøre dette.
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8,1), 1)
Lad mig forklare, hvordan denne formel fungerer.
I funktionen MATCH har vi brugt 1 som [match_type] argument. Dette argument returnerer den største værdi, der er mindre end eller lig med opslagsværdien.
Det betyder, at MATCH -formlen går gennem mærkerne, og så snart den finder et mærkeinterval, der er lig med eller mindre end opslagsmærkernes værdi, stopper den der og returnerer sin position.
Så hvis opslagsmærkeværdien er 20, ville MATCH -funktionen returnere 1, og hvis den er 85, ville den returnere 5.
Og INDEX -funktionen bruger denne positionsværdi til at få karakteren.
VIGTIGT: For at dette skal fungere, skal dine data sorteres i stigende rækkefølge. Hvis det ikke er det, kan du få forkerte resultater.Bemærk, at ovenstående også kan gøres ved hjælp af nedenstående VLOOKUP -formel:
= VLOOKUP (B2, $ E $ 3: $ F $ 8,2, SAND)
Men MATCH -funktionen kan gå et skridt videre, når det kommer til omtrentlig match.
Du kan også have en faldende data og kan bruge INDEX MATCH combo til at finde resultatet. For eksempel, hvis jeg ændrer rækkefølgen på karaktertabellen (som vist herunder), kan jeg stadig finde elevernes karakterer.
For at gøre dette er alt, hvad jeg skal gøre, at ændre [match_type] -argumentet til -1.
Nedenfor er den formel, jeg har brugt:
= INDEX ($ F $ 3: $ F $ 8, MATCH (B2, $ E $ 3: $ E $ 8, -1), 1)VLOOKUP kan også foretage en omtrentlig matchning, men kun når data er sorteret i stigende rækkefølge (men det virker ikke, hvis dataene er sorteret i faldende rækkefølge).
Eksempel 7: Kuffertfølsomme opslag
Hidtil har alle de opslag, vi har foretaget, været ufølsomme for store og små bogstaver.
Det betyder, at uanset om opslagsværdien var Jim eller JIM eller jim, var det ligegyldigt. Du får det samme resultat.
Men hvad nu hvis du vil have opslaget til at være store og små bogstaver.
Dette er normalt tilfældet, når du har store datasæt og mulighed for gentagelse eller forskellige navne/id'er (med den eneste forskel)
Antag for eksempel, at jeg har følgende datasæt af elever, hvor der er to elever med navnet Jim (den eneste forskel er, at den ene er indtastet som Jim og en anden som jim).
Bemærk, at der er to elever med samme navn - Jim (celle A2 og A5).
Da et normalt opslag ikke ville fungere, er du nødt til at foretage et case -sensitive opslag.
Nedenfor er formlen, der giver dig det rigtige resultat. Da dette er en matrixformel, skal du bruge Control + Shift + Enter.
= INDEX ($ B $ 2: $ B $ 11, MATCH (TRUE, EXACT (D3, A2: A11), 0), 1)
Lad mig forklare, hvordan denne formel fungerer.
EXAKT -funktionen søger efter en nøjagtig matchning af opslagsværdien (hvilket er 'jim' i dette tilfælde). Det går igennem alle navne og returnerer FALSKT, hvis det ikke er et match og SANDT, hvis det er et match.
Så output fra EXAKT -funktionen i dette eksempel er - {FALSK; FALSK; FALSK; SAND; FALSK; FALSK; FALSK; FALSK; FALSK; FALSK}
Bemærk, at der kun er ét SANDT, det var da EXAKT -funktionen fandt et perfekt match.
MATCH -funktionen finder derefter positionen TRUE i arrayet returneret af EXACT -funktionen, som er 4 i dette eksempel.
Når vi har positionen, bruger INDEX -funktionen den til at finde mærkerne.
Eksempel 8: Find det tætteste match
Lad os komme lidt videre nu.
Antag, at du har et datasæt, hvor du vil finde den person, der har den erhvervserfaring, der er tættest på den krævede erfaring (nævnt i celle D2).
Selvom opslagsformler ikke er lavet til at gøre dette, kan du kombinere det med andre funktioner (f.eks. MIN og ABS) for at få dette gjort.
Nedenfor er formlen, der finder personen med den erfaring, der er tættest på den nødvendige, og returnerer personens navn. Bemærk, at oplevelsen skal være tættest (som enten kan være mindre eller mere).
= INDEX ($ A $ 2: $ A $ 15, MATCH (MIN (ABS (D2-B2: B15)), ABS (D2- $ B $ 2: $ B $ 15), 0))
Da dette er en matrixformel, skal du bruge Control + Shift + Enter.
Tricket i denne formel er at ændre opslagsværdi og opslagsmateriale for at finde den mindste oplevelsesforskel i krævede og faktiske værdier.
Før jeg forklarer formlen, lad os forstå, hvordan du ville gøre det manuelt.
Du vil gå gennem hver celle i kolonne B og finde forskellen i oplevelsen mellem, hvad der kræves, og det, som en person har. Når du har alle forskellene, finder du den, der er mindst, og henter navnet på den person.
Det er præcis, hvad vi gør med denne formel.
Lad mig forklare.
Opslagningsværdien i MATCH-formlen er MIN (ABS (D2-B2: B15)).
Denne del giver dig den mindste forskel mellem den givne erfaring (som er 2,5 år) og alle de andre oplevelser. I dette eksempel returnerer det 0,3
Bemærk, at jeg har brugt ABS til at sikre, at jeg leder efter det nærmeste (hvilket kan være mere eller mindre end den givne oplevelse).
Nu bliver denne mindste værdi vores opslagsværdi.
Lookup-arrayet i MATCH-funktionen er ABS (D2- $ B $ 2: $ B $ 15).
Dette giver os en række tal, hvorfra 2,5 (den krævede erfaring) er trukket fra.
Så nu har vi en opslagsværdi (0,3) og et opslagsarray ({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 -funktion finder positionen 0,3 i dette array, som også er positionen for den persons navn, der har den nærmeste oplevelse.
Dette positionsnummer bruges derefter af INDEX -funktionen til at returnere personens navn.
Relateret læsning: Find det tætteste match i Excel (eksempler ved hjælp af opslagsformler)
Klik her for at downloade eksempelfilen
Eksempel 9: Brug INDEX MATCH med jokertegn
Hvis du vil slå en værdi op, når der er et delvis match, skal du bruge jokertegn.
Nedenfor er f.eks. Et datasæt med firmanavn og deres markedsværdi, og du vil gerne have markedsværdien. data for de tre virksomheder til højre.
Da dette ikke er nøjagtige matches, kan du ikke foretage et almindeligt opslag i dette tilfælde.
Men du kan stadig få de rigtige data ved at bruge en stjerne (*), som er et jokertegn.
Nedenfor er formlen, der giver dig dataene ved at matche firmanavne fra hovedkolonnen og hente markedsværdien for det.
= INDEX ($ B $ 2: $ B $ 10, MATCH (D2 & ”*”, $ A $ 2: $ A $ 10,0), 1)
Lad mig forklare, hvordan denne formel fungerer.
Da der ikke er nogen nøjagtig matchning af opslagsværdier, har jeg brugt D2 & ”*” som opslagsværdi i funktionen MATCH.
En stjerne er et jokertegn, der repræsenterer et vilkårligt antal tegn. Det betyder, at Apple* i formlen ville betyde enhver tekststreng, der starter med ordet Apple og kan have et hvilket som helst antal tegn efter det.
Så når Æble* bruges som opslagsværdi, og MATCH -formlen leder efter det i kolonne A, returnerer det placeringen af 'Apple Inc.', da det starter med ordet Apple.
Du kan også bruge jokertegn til at finde tekststrenge, hvor opslagsværdien er imellem. Hvis du f.eks. Bruger * Apple * som opslagsværdi, finder den enhver streng, der har ordet æble hvor som helst.
Bemærk: Denne teknik fungerer godt, når du kun har én forekomst af matchning. Men hvis du har flere forekomster af matchning (f.eks. Apple Inc og Apple Corporation, returnerer funktionen MATCH kun placeringen af den første matchende forekomst.
Eksempel 10: Trevejsopslag
Dette er en avanceret brug af INDEX MATCH, men jeg vil stadig dække det for at vise dig kraften i denne kombination.
Husk, at jeg sagde, at INDEX -funktionen har to syntakser:
= INDEX (array, row_num, [col_num]) = INDEX (array, row_num, [col_num], [area_num])
Hidtil i alle vores eksempler har vi kun brugt det første.
Men for et trevejsopslag skal du bruge den anden syntaks.
Lad mig først forklare, hvad et trevejsudseende betyder.
I et tovejsopslag bruger vi INDEX MATCH-formlen til at få karaktererne, når vi har elevens navn og emnets navn. For eksempel er at hente Jim-karaktererne i matematik et tovejsopslag.
Et trevejs-look ville tilføre det en anden dimension. Antag for eksempel, at du har et datasæt som vist nedenfor, og du vil vide Jim's score i matematik ved midtvejseksamen, så ville dette være trevejsopslag.
Nedenfor er formlen, der giver 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))))
Ovenstående formel kontrollerede for tre ting - elevens navn, emnet og eksamen. Når den har fundet den rigtige værdi, returnerer den den i cellen.
Lad mig forklare, hvordan denne formel fungerer ved at opdele formlen i dele.
- array - ($ B $ 3: $ D $ 7, $ B $ 11: $ D $ 15, $ B $ 19: $ D $ 23): I stedet for at bruge et enkelt array har jeg i dette tilfælde brugt tre arrays inden for parentes.
- row_num - MATCH ($ F $ 5, $ A $ 3: $ A $ 7,0): MATCH -funktion bruges til at finde placeringen af elevens navn i celle $ F $ 5 på listen over elevens navn.
- col_num - MATCH (G $ 4, $ B $ 2: $ D $ 2,0): MATCH -funktion bruges til at finde emnets placering i cellen $ B $ 2 på listen over emnets navn.
- [area_num] - IF (G $ 3 = ”Unit Test”, 1, IF (G $ 3 = ”Mid Term”, 2,3)): Arealværdien fortæller INDEX -funktionen, hvilken af de tre arrays der skal bruges til at hente værdien. Hvis undersøgelsen er Enhedsterm, returnerer IF -funktionen 1, og INDEX -funktionen vil bruge det første array til at hente værdien. Hvis eksamen er midtvejs, returnerer IF-formlen 2, ellers returnerer den 3.
Dette er et avanceret eksempel på at bruge INDEX MATCH, og det er usandsynligt, at du finder en situation, når du skal bruge dette. Men det er stadig godt at vide, hvad Excel -formler kan.
Klik her for at downloade eksempelfilen
Hvorfor er INDEX/MATCH bedre end VLOOKUP?
Eller er det?
Ja, det er - i de fleste tilfælde.
Jeg vil forelægge min sag om et stykke tid.
Men før jeg gør det, lad mig sige dette - VLOOKUP er en yderst nyttig funktion, og jeg elsker den. Det kan mange ting i Excel, og jeg bruger det nu og da selv. Når det er sagt, betyder det ikke, at der ikke kan være noget bedre, og INDEX/MATCH (med mere fleksibilitet og funktionaliteter) er bedre.
Så hvis du vil lave et grundlæggende opslag, er det bedre at bruge VLOOKUP.
INDEX/MATCH er VLOOKUP på steroider. Og når du først har lært INDEX/MATCH, foretrækker du måske altid at bruge det (især på grund af den fleksibilitet, det har).
Uden at strække det for langt, lad mig hurtigt give dig årsagerne til, at INDEX/MATCH er bedre end VLOOKUP.
INDEX/MATCH kan se til venstre (såvel som til højre) af opslagsværdien
Jeg dækkede det i et af eksemplerne ovenfor.
Hvis du har en værdi, der er til venstre for opslagsværdien, kan du ikke gøre det med VLOOKUP
I hvert fald ikke med bare VLOOKUP.
Ja, du kan kombinere VLOOKUP med andre formler og få det gjort, men det bliver kompliceret og rodet.
INDEX/MATCH, på den anden side, er lavet til at slå op overalt (det være sig til venstre, højre, op eller ned)
INDEX/MATCH kan arbejde med lodrette og vandrette intervaller
Igen, med fuld respekt for VLOOKUP, er det ikke skabt til at gøre dette.
Tross alt står V i VLOOKUP for lodret.
VLOOKUP kan kun gå igennem data, der er lodrette, mens INDEX/MATCH kan gå igennem data både lodret og vandret.
Selvfølgelig er der HLOOKUP -funktionen til at tage sig af vandret opslag, men det er ikke VLOOKUP så … ikke?
Jeg kan godt lide, at INDEX MATCH -kombinationsboksen er fleksibel nok til at arbejde med både lodrette og vandrette data.
VLOOKUP kan ikke fungere med faldende data
Når det kommer til den omtrentlige kamp, er VLOOKUP og INDEX/MATCH på samme niveau.
Men INDEX MATCH tager pointen, da den også kan håndtere data, der er i faldende rækkefølge.
Jeg viser dette i et af eksemplerne i denne vejledning, hvor vi skal finde elevernes karakter baseret på karaktertabellen. Hvis tabellen er sorteret i faldende rækkefølge, ville VLOOKUP ikke fungere (men INDEX MATCH ville).
INDEX/MATCH kan være lidt hurtigere
Jeg vil være sandfærdig. Jeg kørte ikke selv denne test.
Jeg stoler på den visdom, en Excel -mester er - Charley Kyd.
Forskellen i hastighed i VLOOKUP og INDEX/MATCH er næppe mærkbar, når du har små datasæt. Men hvis du har tusinder af rækker og mange kolonner, kan dette være en afgørende faktor.
I sin artikel siger Charley Kyd:
”I værste fald er INDEX-MATCH-metoden omtrent lige så hurtig som VLOOKUP; når det er bedst, er det meget hurtigere. ”
INDEX/MATCH er uafhængig af den faktiske kolonneposition
Hvis du har et datasæt som vist herunder, mens du henter karakteren af Jim i fysik, kan du gøre det ved hjælp af VLOOKUP.
Og for at gøre det kan du angive kolonnetallet som 3 i VLOOKUP.
Alt er fint.
Men hvad nu hvis jeg sletter matematik -kolonnen.
I så fald vil VLOOKUP -formlen gå i stykker.
Hvorfor? - Fordi det var hårdkodet at bruge den tredje kolonne, og når jeg sletter en kolonne imellem, bliver den tredje kolonne den anden kolonne.
I dette tilfælde er det bedre at bruge INDEX/MATCH, da du kan gøre kolonnetallet dynamisk ved at bruge MATCH. Så i stedet for et kolonnenummer søger det efter emnets navn og bruger det til at returnere kolonnenummeret.
Sikkert kan du gøre det ved at kombinere VLOOKUP med MATCH, men hvis du alligevel kombinerer, hvorfor ikke gøre det med INDEX, som er meget mere fleksibel.
Når du bruger INDEX/MATCH, kan du sikkert indsætte/slette kolonner i dit datasæt.
På trods af alle disse faktorer er der en grund til, at VLOOKUP er så populær.
Og det er en stor grund.
VLOOKUP er lettere at bruge
VLOOKUP tager højst fire argumenter. Hvis du kan vikle dit hoved omkring disse fire, er du klar til at gå.
Og da de fleste af de grundlæggende opslagssager også håndteres af VLOOKUP, er det hurtigt blevet den mest populære Excel -funktion.
Jeg kalder det kongen af Excel -funktioner.
INDEX/MATCH er derimod lidt sværere at bruge. Du kan få et hæng, hvis det begynder at bruge det, men for en nybegynder er VLOOKUP langt lettere at forklare og lære.
Og dette er ikke et nulsumsspil.
Så hvis du er ny inden for opslagsverdenen og ikke ved, hvordan du bruger VLOOKUP, er det bedre at lære det.
Jeg har en detaljeret guide til brug af VLOOKUP i Excel (med masser af eksempler)
Min hensigt med denne artikel er ikke at stille to fantastiske funktioner op mod hinanden. Jeg ville vise dig kraften i INDEX MATCH combo og alle de store ting, den kan gøre.
Håber du fandt denne artikel nyttig.
Fortæl mig dine tanker i kommentarfeltet, og lad mig vide, hvis du finder en fejl i denne vejledning.
