Hoppa till huvudinnehåll

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

Populära funktioner: Hitta, markera eller identifiera dubbletter  |  Ta bort tomma rader  |  Kombinera kolumner eller celler utan att förlora data  |  Rund utan formel ...
Super VLookup: Flera kriterier  |  Multipelt värde  |  Över flera ark  |  Fuzzy Lookup...
Adv. Rullgardinslista: Enkel rullgardinslista  |  Beroende rullgardinslista  |  Flervals-rullgardinslista...
Kolumnhanterare: Lägg till ett specifikt antal kolumner  |  Flytta kolumner  |  Växla synlighetsstatus för dolda kolumner  Jämför kolumner med Välj Samma och olika celler ...
Utvalda funktioner: Rutnätsfokus  |  Designvy  |  Stor formelbar  |  Arbetsbok & Bladhanterare | Resursbibliotek (Automatisk text)  |  Datumväljare  |  Kombinera arbetsblad  |  Kryptera/Dekryptera celler  |  Skicka e-postmeddelanden efter lista  |  Superfilter  |  Specialfilter (filtrera fet/kursiv/genomstruken...) ...
Topp 15 verktygssatser12 text verktyg (lägga till text, Ta bort tecken ...)  |  50 + Diagram Typer (Gantt Chart ...)  |  40+ Praktiskt Formler (Beräkna ålder baserat på födelsedag ...)  |  19 Införande verktyg (Infoga QR-kod, Infoga bild från sökväg ...)  |  12 Konvertering verktyg (Siffror till ord, Valutaväxling ...)  |  7 Slå ihop och dela verktyg (Avancerade kombinera rader, Dela upp Excel-celler ...)  |  ... och mer

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.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations