Generisk formel
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Sammendrag
For å slå opp verdier med INDEX og MATCH, ved å bruke flere kriterier, kan du bruke en matriseformel. I eksemplet vist er formelen i H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Merk: dette er en matriseformel, og må angis med kontroll + skift + enter, unntatt i Excel 365.
Forklaring
Dette er en mer avansert formel. For grunnleggende informasjon, se Hvordan bruke INDEX og MATCH.
Normalt er en INDEX MATCH-formel konfigurert med MATCH satt til å se gjennom et kolonneområde og gi et samsvar basert på gitte kriterier. Uten å sammenkoble verdier i en hjelpekolonne, eller i selve formelen, er det ingen måte å oppgi mer enn ett kriterium.
Denne formelen omgir denne begrensningen ved å bruke boolsk logikk for å lage en matrise med ener og nuller for å representere rader som samsvarer med alle tre kriteriene, og deretter bruke MATCH for å matche den første som ble funnet. Den midlertidige matrisen med ener og nuller genereres med denne kodebiten:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Her sammenligner vi varen i H5 mot alle varene, størrelsen i H6 mot alle størrelser, og fargen i H7 mot alle farger. Det opprinnelige resultatet er tre matriser med SANNE / FALSE resultater som dette:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tips: bruk F9 for å se disse resultatene. Bare velg et uttrykk i formellinjen, og trykk F9.
Matematikkoperasjonen (multiplikasjon) forvandler de SANNE FALSKE verdiene til 1s og 0s:
(1;1;1;0;0;0;1)*(0;0;1;0;0;1;0)*(1;0;1;0;0;0;1)
Etter multiplikasjon har vi en enkelt matrise som dette:
(0;0;1;0;0;0;0)
som mates inn i MATCH-funksjonen som oppslagsmatrise, med en oppslagsverdi på 1:
MATCH(1,(0;0;1;0;0;0;0))
På dette punktet er formelen en standard INDEX MATCH-formel. MATCH-funksjonen returnerer 3 til INDEKS:
=INDEX(E5:E11,3)
og INDEX returnerer et endelig resultat på $ 17,00.
Array visualisering
Arrangementene forklart ovenfor kan være vanskelige å visualisere. Bildet nedenfor viser grunnideen. Kolonnene B, C og D tilsvarer dataene i eksemplet. Kolonne F opprettes ved å multiplisere de tre kolonnene sammen. Det er matrisen overlevert til MATCH.
Ikke-array-versjon
Det er mulig å legge til en annen INDEKS til denne formelen, og unngå behovet for å gå inn som en matriseformel med kontroll + skift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
INDEX-funksjonen kan håndtere matriser naturlig, så den andre INDEX legges bare til for å "fange" matrisen som ble opprettet med den boolske logikkoperasjonen og returnere den samme matrisen igjen til MATCH. For å gjøre dette er INDEX konfigurert med null rader og en kolonne. Nullradetriket får INDEX til å returnere kolonne 1 fra matrisen (som uansett allerede er en kolonne).
Hvorfor vil du ha ikke-array-versjonen? Noen ganger glemmer folk å legge inn en matriseformel med kontroll + shift + enter, og formelen gir et feil resultat. Så en ikke-matriseformel er mer "skuddsikker". Avviket er imidlertid en mer kompleks formel.
Merk: I Excel 365 er det ikke nødvendig å angi matriseformler på en spesiell måte.
