Note: The other languages of the website are Google-translated. Back to English
English English

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.

index matcha flera kolumner 1

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.

index matcha flera kolumner 2

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-funktion

Excel INDEX -funktionen returnerar det visade värdet baserat på en given position från ett intervall eller en array.

Excel MATCH-funktion

Excel MATCH -funktionen söker efter ett specifikt värde i ett cellintervall och returnerar värdets relativa position.

Excel MMULT -funktion

Excel MMULT-funktionen returnerar matrisprodukten av två matriser. Arrayresultatet har samma antal rader som array1 och samma antal kolumner som array2.

Excel TRANSPOSE -funktion

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.

Excel KOLONN-funktion

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

Vill du slutföra ditt dagliga arbete snabbt och perfekt? Kutools för Excel ger 300 kraftfulla avancerade funktioner (kombinera arbetsböcker, summera efter färg, dela cellinnehåll, konvertera datum och så vidare ...) och spara 80% tid åt dig.

  • Designad för 1500 arbetsscenarier, hjälper dig att lösa 80% Excel-problem.
  • Minska tusentals tangentbord och musklick varje dag, lindra dina trötta ögon och händer.
  • Bli en expert på 3 minuter. Behöver inte längre komma ihåg några smärtsamma formler och VBA-koder.
  • 30 dagars obegränsad gratis provperiod. 60-dagars pengarna tillbaka-garanti. Gratis uppgradering och support i 2 år.
Ribbon of Excel (med Kutools för Excel installerat)

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 tittar på och redigerar flera dokument.
  • Ger effektiva flikar till Office (inkluderar Excel), precis som Chrome, Firefox och ny Internet Explorer.
Skärmdump av Excel (med Office-fliken installerad)
Sortera kommentarer efter
Kommentarer (0)
Inga betyg än. Bli först med att betygsätta!
Det finns inga kommentarer här ännu
Lämna dina kommentarer
Postar som gäst
×
Betygsätt detta inlägg:
0   Tecken
Föreslagna platser