INDEX og MATCH med flere arrays
Lad os sige, at du har flere tabeller med samme billedtekster som vist nedenfor, at slå værdier, der matcher giv-kriterierne fra disse tabeller, kan være en hård opgave for dig. I denne øvelse vil vi tale om, hvordan man slår en værdi på tværs af flere arrays, intervaller eller grupper ved at matche specifikke kriterier med INDEX, MATCH , VÆLGE funktioner.
Hvordan slår man en værdi op på tværs af flere arrays?
At kende ledere af forskellige grupper, der tilhører forskellige afdelinger, kan du først bruge funktionen VÆLG til at målrette den tabel, som lederens navn skal returneres fra. MATCH-funktionen vil så finde ud af lederens position i tabellen, hvor han/hun hører til. Endelig vil INDEX-funktionen hente lederen baseret på positionsoplysningerne plus den specifikke kolonne, hvor ledernes navne er opført.
Generisk syntaks
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Tallet CHOOSE bruges til at angive en matrix fra listen matrix1, matrix2,... at returnere resultatet fra.
- matrix1, matrix2,...: De arrays, som resultatet skal returneres fra. Her refereres til de tre tabeller.
- opslagsværdi: Værdien kombinationsformlen brugte til at finde positionen for dens tilsvarende leder. Her refereres til den givne gruppe.
- opslag_array: Celleområdet, hvor opslagsværdi er opført. Her refererer til grupperækken. Bemærk: Du kan bruge gruppeintervallet fra enhver afdeling, da de alle er ens, og vi skal blot have stillingsnummeret.
- kolonnenummer: Den kolonne du angiver, hvorfra du vil hente data.
At kende leder af gruppe D, der hører til afdeling A, kopier eller indtast formlen nedenfor i celle G5, og tryk på Indtast for at få resultatet:
=INDEKS(VÆLG(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$ B $ 5: $ B $ 8, 0),2)
√ Bemærk: Dollartegnene ($) ovenfor angiver absolutte referencer, hvilket betyder, at navn og klasseområder i formlen ikke ændres, når du flytter eller kopierer formlen til andre celler. Når du har indtastet formlen, skal du trække udfyldningshåndtaget ned for at anvende formlen på nedenstående celler og derefter ændre array_num derfor.
Forklaring af formlen
=INDEX(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$B$5:$B$8,0),2)
- CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20): Funktionen VÆLG returnerer 1st array fra de tre arrays, der er angivet i formlen. Så det vender tilbage $B$5:$C$8, dvs dataområde for afdeling A.
- MATCH(F5;$B$5:$B$8,0): Match_type 0 tvinger MATCH-funktionen til at returnere positionen for den første match af Gruppe D, værdien i cellen F5, i arrayet $ B $ 5: $ B $ 8, Hvilket er 4.
- INDEKS(CHOOSE(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5;$B$5:$B$8,0),2) = INDEX ($B$5:$C$8,4,2): INDEX -funktionen henter værdien ved skæringspunktet mellem 4række og 2nd kolonne i området $B$5:$C$8, Hvilket er Emily.
For at undgå at ændre sig array_num i formlen, hver gang du kopierer den, kan du bruge hjælpekolonnen, kolonnen D. Formlen ville være sådan:
=INDEKS(VÆLG(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$ B $ 5: $ B $ 8, 0),2)
√ Bemærk: Tallene 1, 2, 3 i hjælpekolonnen angives matrix1, matrix2, matrix3 inde i VÆLG-funktionen.
Relaterede funktioner
Excel INDEX -funktionen returnerer den viste værdi baseret på en given position fra et område eller en matrix.
Funktionen Excel MATCH søger efter en bestemt værdi i et celleområde og returnerer værdiens relative position.
Funktionen VÆLG returnerer en værdi fra værdilisteargumentet baseret på det givne indeksnummer. F.eks. Returnerer VÆLG (3, ”Apple”, ”Fersken”, ”Orange”) Orange, indeksnummeret er 3, og Orange er den tredje værdi efter indeksnummeret i funktionen.
Relaterede formler
Opslagsværdier fra et andet regneark eller en anden projektmappe
Hvis du ved, hvordan du bruger VLOOKUP-funktionen til at søge efter værdier i et regneark, vil ikke opsøgningsværdier fra et andet regneark eller projektmappe være et problem for dig.
I mange tilfælde kan det være nødvendigt at indsamle data på tværs af flere regneark til opsummering. Med kombinationen af VLOOKUP-funktionen og INDIRECT-funktionen kan du oprette en formel til at slå specifikke værdier på tværs af regneark med et dynamisk arknavn.
Opslag med flere kriterier med INDEX og MATCH
Når man behandler en stor database i et Excel -regneark med flere kolonner og rækketekster, er det altid svært at finde noget, der opfylder flere kriterier. I dette tilfælde kan du bruge en matrixformel med INDEX- og MATCH -funktionerne.
De bedste Office-produktivitetsværktøjer
Kutools til Excel - Hjælper dig med at skille dig ud fra mængden
Kutools til Excel kan prale af over 300 funktioner, At sikre, at det, du har brug for, kun er et klik væk...
Fanen Office - Aktiver læsning og redigering af faner i Microsoft Office (inkluderer Excel)
- Et sekund til at skifte mellem snesevis af åbne dokumenter!
- Reducer hundreder af museklik for dig hver dag, og farvel med musens hånd.
- Øger din produktivitet med 50 %, når du ser og redigerer flere dokumenter.
- Giver effektive faner til Office (inkluderer Excel), ligesom Chrome, Edge og Firefox.