INDEX och MATCH med flera arrayer
Låt oss säga att du har flera tabeller med samma bildtexter som visas nedan, att slå upp värden som matchar givkriterierna från dessa tabeller kan vara svårt för dig. I den här handledningen kommer vi att prata om hur man slår upp ett värde över flera arrayer, intervall eller grupper genom att matcha specifika kriterier med INDEX, MATCH och VÄLJA funktioner.
Hur slår man upp ett värde över flera arrayer?
Att känna till ledare för olika grupper som tillhör olika avdelningar, kan du först använda funktionen VÄLJ för att rikta in sig på tabellen att returnera ledarens namn från. MATCH-funktionen kommer då att ta reda på positionen för ledaren i tabellen där han/hon tillhör. Slutligen kommer INDEX-funktionen att hämta ledaren baserat på positionsinformationen plus den specifika kolumnen där ledarnas namn är listade.
Generisk syntax
=INDEX(CHOOSE(array_num,array1,array2,…),MATCH(lookup_value,lookup_array,0),column_num)
- array_num: Siffran CHOOSE som används för att indikera en array från listan array1, array2,... att returnera resultatet från.
- array1, array2,...: Arrayerna att returnera resultatet från. Här hänvisar till de tre tabellerna.
- lookup_value: Värdet som kombinationsformeln använde för att hitta positionen för dess motsvarande ledare. Här hänvisar till den givna gruppen.
- lookup_array: Utbudet av celler där letauppvärde är listad. Här avses grupputbudet. Notera: Du kan använda gruppomfånget från vilken avdelning som helst eftersom de alla är lika och vi behöver bara få positionsnumret.
- kolumnnummer: Den kolumn du anger från vilken du vill hämta data.
Att känna till ledare för grupp D som tillhör avdelning A, kopiera eller skriv in formeln nedan i cellen G5 och tryck ange för att få resultatet:
=INDEX(VÄLJ(1,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$ B $ 5: $ B $ 8,0),2)
√ Notera: Dollartecknen ($) ovan anger absoluta referenser, vilket betyder att namnet och klassintervallen i formeln inte kommer att ändras när du flyttar eller kopierar formeln till andra celler. När du har angett formeln drar du fyllningshandtaget nedåt för att tillämpa formeln på cellerna nedan och ändrar sedan array_num följaktligen.
Förklaring av formeln
=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ÄLJ returnerar 1st array från de tre arrayer som anges i formeln. Så det kommer tillbaka $B$5:$C$8, dvs dataintervall för avdelning A.
- MATCH(F5,$B$5:$B$8,0): Matchningstypen 0 tvingar MATCH-funktionen att returnera positionen för den första matchningen av Grupp D, värdet i cellen F5, i arrayen $ B $ 5: $ B $ 8, vilket är 4.
- INDEX(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 hämtar värdet i skärningspunkten mellan 4raden och 2andra kolumnen i intervallet $B$5:$C$8, vilket är Emily.
För att slippa byta array_num i formeln varje gång du kopierar den kan du använda hjälpkolumnen, kolumnen D. Formeln skulle se ut så här:
=INDEX(VÄLJ(D5,$B$5:$C$8,$B$11:$C$14,$B$17:$C$20),MATCH(F5,$ B $ 5: $ B $ 8,0),2)
√ Obs: Siffrorna 1, 2, 3 i hjälpkolumnen anger du array1, array2, array3 inuti VÄLJ-funktionen.
Relaterade funktioner
Excel INDEX -funktionen returnerar det visade värdet baserat på en given position från ett intervall eller en array.
Excel MATCH -funktionen söker efter ett specifikt värde i ett cellintervall och returnerar värdets relativa position.
Funktionen VÄLJ returnerar ett värde från värdelistan argument baserat på det angivna indexnumret. Till exempel returnerar VÄLJ (3, ”Apple”, ”Peach”, ”Orange”) Orange, indexnumret är 3 och Orange är det tredje värdet efter indexnummer i funktionen.
Relaterade formler
Sökvärden från ett annat kalkylblad eller arbetsbok
Om du vet hur du använder VLOOKUP-funktionen för att söka efter värden i ett kalkylblad, kommer inte uppslagsvärden från ett annat kalkylblad eller arbetsbok vara ett problem för dig.
I många fall kan du behöva samla in data över flera kalkylblad för sammanfattning. Med kombinationen av VLOOKUP-funktionen och INDIRECT-funktionen kan du skapa en formel för att slå upp specifika värden över kalkylblad med dynamiskt arknamn.
Sökning med flera kriterier med INDEX och MATCH
När man har att göra med en stor databas i ett Excel-kalkylblad med flera kolumner och radtexter är det alltid svårt att hitta något som uppfyller flera kriterier. I det här fallet kan du använda en matrisformel med funktionerna INDEX och MATCH.
De bästa Office-produktivitetsverktygen
Kutools för Excel - Hjälper dig att sticka ut från mängden
Kutools för Excel har över 300 funktioner, Se till att det du behöver bara är ett klick bort...
Fliken Office - Aktivera läsning och redigering av flikar i Microsoft Office (inkluderar Excel)
- En sekund att växla mellan dussintals öppna dokument!
- Minska hundratals musklick åt dig varje dag, säg adjö till mushanden.
- Ökar din produktivitet med 50 % när du visar och redigerar flera dokument.
- Ger effektiva flikar till Office (inklusive Excel), precis som Chrome, Edge och Firefox.