INDEX och MATCH över flera kolumner
För att slå upp ett värde genom att matcha över flera kolumner, en matrisformel baserad på INDEX och MATCH funktioner som innehåller MMULT, FLYTTA OM och KOLUMN kommer att göra dig en tjänst.
Hur slår man upp ett värde genom att matcha över flera kolumner?
För att fylla i motsvarande klass för varje elev som ovanstående tabell visas, där informationen listas i flera kolumner, kan du först använda tricket med funktionen MMULT, TRANSPOSE och COLUMN för att skapa en matrismatris. Sedan kommer MATCH-funktionen att ge dig positionen för ditt uppslagsvärde, vilket kommer att matas till INDEX för att hämta det värde du letar efter i arrayen.
Generisk syntax
=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))
√ Obs! Detta är en matrisformel som kräver att du anger med ctrl + shift + ange.
- return_range: Området där du vill att formeln ska returnera klassinformationen från. Här avses klassutbudet.
- lookup_value: Värdet som formeln använde för att hitta motsvarande klassinformation. Här hänvisar till förnamnet.
- lookup_array: Utbudet av celler där letauppvärde är listad; Området med värden att jämföra med letauppvärde. Här hänvisar till namnintervallet.
- match_type 0: Tvingar MATCH att hitta det första värdet som exakt är lika med letauppvärde.
För att hitta klass av Jimmy, vänligen kopiera eller ange formeln nedan i cellen H5 och tryck på ctrl + shift + ange för att få resultatet:
= INDEX ($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSERA(KOLUMN($C$5:$E$7)^0)),0)))
√ Notera: Dollartecknen ($) ovan indikerar absoluta referenser, vilket betyder att namnet och klassintervallen i formeln inte kommer att ändras när du flyttar eller kopierar formeln till andra celler. Observera att du inte ska lägga till dollartecken i cellreferensen som representerar uppslagsvärdet, eftersom du vill att det ska vara relativt när du kopierar det till andra celler. När du har angett formeln drar du fyllningshandtaget nedåt för att tillämpa formeln på cellerna nedan.
Förklaring av formeln
=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))
- --($C$5:$E$7=G5): Detta segment kontrollerar varje värde i intervallet $C$5:$E$7 om de är lika med värdet i cellen G5, och genererar en TRUE och FALSE array så här:
{TRUE,FALSE,FALSE;FALSE,FALSE,FALSE;FALSE,FALSE,FALSE}.
Det dubbla negativa kommer sedan att konvertera TRUEs och FALSEs till 1:or och 0:or för att ge en array så här:
{1,0,0; 0,0,0; 0,0,0}. - KOLUMN($C$5:$E$7): Funktionen COLUMN returnerar kolumnnumren för intervallet $C$5:$E$7 i en array som denna: 3,4,5 {}.
- TRANSPONERA(KOLUMN($C$5:$E$7)^0) = TRANSPONERA(3,4,5 {}^0): Efter att ha höjt potensen till 0 kommer alla siffror i arrayen {3,4,5} att omvandlas till 1: {1,1,1}. TRANSPOSE-funktionen konverterar sedan kolumnmatrisen till en radmatris så här: {1; 1; 1}.
- MMULT(--($C$5:$E$7=G5),TRANSPONERA(KOLUMN($C$5:$E$7)^0)) = MMULT({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): Funktionen MMULT returnerar matrisprodukten för de två arrayerna så här: {1; 0; 0}.
- MATCH (1,MMULT(--($C$5:$E$7=G5),TRANSPONERA(KOLUMN($C$5:$E$7)^0)), 0) = MATCH (1,{1; 0; 0}, 0): Matchningstypen 0 tvingar MATCH-funktionen att returnera positionen för den första matchningen av 1 i matrisen {1; 0; 0}, vilket är 1.
- INDEX($ B $ 5: $ B $ 7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPONERA(KOLUMN($C$5:$E$7)^0)), 0))) = INDEX ($ B $ 5: $ B $ 7,1): INDEX -funktionen returnerar 1st värde i klassintervallet $ B $ 5: $ B $ 7, vilket är A.
För att enkelt slå upp ett värde genom att matcha över flera kolumner kan du också använda vårt professionella Excel-tillägg Kutools för Excel. Se instruktionerna här för att utföra uppdraget.
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.
Excel MMULT-funktionen returnerar matrisprodukten av två matriser. Arrayresultatet har samma antal rader som array1 och samma antal kolumner som array2.
Excel TRANSPOSE-funktionen roterar orienteringen av ett område eller en matris. Till exempel kan den rotera en tabell som är arrangerad horisontellt i rader till vertikalt i kolumner eller vice versa.
Funktionen COLUMN returnerar antalet kolumner som formeln visas eller returnerar kolumnnumret för given referens. Till exempel returnerar formel =KOLUMN(BD) 56.
Relaterade formler
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.
Tvåvägsuppslag med INDEX och MATCH
För att söka efter något över både rader och kolumner i Excel, eller vi säger att slå upp ett värde i skärningspunkten mellan den specifika raden och kolumnen, kan vi använda hjälp av funktionerna INDEX och MATCH.
Sök upp närmaste matchningsvärde med flera kriterier
I vissa fall kan du behöva leta upp närmaste eller ungefärliga matchningsvärde baserat på mer än ett kriterium. Med kombinationen av INDEX-, MATCH- och IF -funktioner kan du snabbt få det gjort i Excel.
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.
