Generisk formel
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Sammanfattning
För att slå värden med INDEX och MATCH, med flera kriterier, kan du använda en matrisformel. I exemplet som visas är formeln i H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Obs: detta är en matrisformel och måste anges med kontroll + shift + enter, utom i Excel 365.
Förklaring
Detta är en mer avancerad formel. För grunder, se Hur man använder INDEX och MATCH.
Normalt är en INDEX MATCH-formel konfigurerad med MATCH inställd för att titta igenom ett kolumnområde och ge en matchning baserat på givna kriterier. Utan att sammanfoga värden i en hjälpkolumn eller i själva formeln finns det inget sätt att tillhandahålla mer än ett kriterium.
Denna formel kringgår denna begränsning genom att använda boolesk logik för att skapa en matris med enor och nollor som representerar rader som matchar alla tre kriterierna och sedan använder MATCH för att matcha den första som hittades. Den tillfälliga matrisen med enor och nollor genereras med detta utdrag:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Här jämför vi artikeln i H5 mot alla artiklar, storleken i H6 mot alla storlekar och färgen i H7 mot alla färger. Det ursprungliga resultatet är tre matriser med SANT / FALSKT resultat så här:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tips: använd F9 för att se dessa resultat. Välj bara ett uttryck i formelfältet och tryck på F9.
Den matematiska operationen (multiplikation) förvandlar de Sanna FALSKA värdena till 1s och 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Efter multiplikation har vi en enda matris så här:
(0;0;1;0;0;0;0)
som matas in i MATCH-funktionen som uppslagsmatris, med ett uppslagsvärde 1:
MATCH(1,(0;0;1;0;0;0;0))
Vid denna tidpunkt är formeln en standardformel för INDEX MATCH. MATCH-funktionen returnerar 3 till INDEX:
=INDEX(E5:E11,3)
och INDEX returnerar ett slutresultat på $ 17,00.
Arrayvisualisering
Arrangemangen som beskrivs ovan kan vara svåra att visualisera. Bilden nedan visar grundidén. Kolumnerna B, C och D motsvarar uppgifterna i exemplet. Kolumn F skapas genom att multiplicera de tre kolumnerna tillsammans. Det är matrisen som överlämnas till MATCH.
Icke-array-version
Det är möjligt att lägga till ytterligare INDEX till denna formel, vilket undviker behovet av att ange som en matrisformel med kontroll + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
INDEX-funktionen kan hantera matriser inbyggt, så den andra INDEX läggs bara till för att "fånga" den matris som skapats med den booleska logiska operationen och återge samma matris igen till MATCH. För att göra detta är INDEX konfigurerad med noll rader och en kolumn. Nollradstricket får INDEX att returnera kolumn 1 från matrisen (som redan är en kolumn ändå).
Varför vill du ha den icke-array-versionen? Ibland glömmer folk att ange en matrisformel med kontroll + shift + enter, och formeln returnerar ett felaktigt resultat. Så en formel som inte är array är mer "skottsäker". Avvägningen är dock en mer komplex formel.
Obs! I Excel 365 är det inte nödvändigt att ange matrisformler på ett speciellt sätt.
