Generisk formel
(=INDEX(rng1,MATCH(1,MMULT(--(rng2=critera),TRANSPOSE(COLUMN(rng2)^0)),0)))
Sammanfattning
För att slå upp ett värde genom att matcha flera kolumner kan du använda en matrisformel baserad på MMULT, TRANSPOSE, COLUMN och INDEX. I exemplet som visas är formeln i H4:
(=INDEX(groups,MATCH(1,MMULT(--(names=G4),TRANSPOSE(COLUMN(names)^0)),0)))
där "namn" är det namngivna området C4: E7 och "grupper" är det namngivna området B4: B7. Formeln returnerar gruppen som varje namn tillhör.
Obs: detta är en matrisformel och måste anges med kontrollskiftet.
Förklaring
Arbetar inifrån och ut, de logiska kriterierna som används i denna formel är:
--(names=G4)
där namnen är det namngivna intervallet C4: E7. Detta genererar ett SANT / FALSKT resultat för varje värde i data, och det dubbla negativet tvingar de Sanna FALSKA värdena till 1 och 0 för att ge en matris så här:
(0,0,0;1,0,0;0,0,0;0,0,0)
Denna matris är 4 rader med 3 kolumner, som matchar strukturen för "namn".
En andra array skapas med detta uttryck:
TRANSPOSE(COLUMN(names)^0))
Funktionen KOLONN används för att skapa en numerisk matris med 3 kolumner och 1 rad, och TRANSPOSE konverterar denna matris till 1 kolumn och 3 rader. Att höja kraften till noll omvandlar helt enkelt alla tal i matrisen till 1. MMULT-funktionen används sedan för att utföra matrismultiplikation:
MMULT((0,0,0;1,0,0;0,0,0;0,0,0),(1;1;1))
och det resulterande går in i MATCH-funktionen som array, med 1 som uppslagsvärde:
MATCH(1,(0;1;0;0),0)
MATCH-funktionen returnerar positionen för den första matchningen, vilket motsvarar raden i den första matchande raden som uppfyller angivna kriterier. Detta matas in i INDEX som radnummer, med det namngivna intervallet "grupper" som array:
=INDEX(groups,2)
Slutligen returnerar INDEX "Bear", gruppen Adam tillhör.
Bokstavlig innehåller för kriterier
För att söka efter specifika textvärden istället för en exakt matchning kan du använda ISNUMBER- och SEARCH-funktionerna tillsammans. För att till exempel matcha celler som innehåller "äpple" kan du använda:
=ISNUMBER(SEARCH("apple",data))
Denna formel förklaras här.
