Generisk formel
(=INDEX(range1,MATCH(1,(A1=range2)*(B1=range3)*(C1=range4),0)))
Resumé
For at slå værdier op med INDEX og MATCH ved hjælp af flere kriterier kan du bruge en matrixformel. I det viste eksempel er formlen i H8:
(=INDEX(E5:E11,MATCH(1,(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11),0)))
Bemærk: dette er en matrixformel og skal indtastes med kontrol + skift + enter, undtagen i Excel 365.
Forklaring
Dette er en mere avanceret formel. For grundlæggende oplysninger, se Sådan bruges INDEX og MATCH.
Normalt er en INDEX MATCH-formel konfigureret med MATCH, der er indstillet til at se gennem et en-søjleområde og give et match baseret på givne kriterier. Uden sammenkædning af værdier i en hjælpekolonne eller i selve formlen er der ingen måde at levere mere end et kriterium på.
Denne formel omgår denne begrænsning ved at bruge boolsk logik til at oprette en matrix med ener og nuller til at repræsentere rækker, der matcher alle 3 kriterier, og derefter bruge MATCH til at matche den første 1 fundet. Den midlertidige matrix med en og nuller genereres med dette uddrag:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)
Her sammenligner vi varen i H5 med alle varer, størrelsen i H6 mod alle størrelser og farven i H7 mod alle farver. Det oprindelige resultat er tre arrays med SAND / FALSK resultater som denne:
(TRUE;TRUE;TRUE;FALSE;FALSE;FALSE;TRUE)*(FALSE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE)*(TRUE;FALSE;TRUE;FALSE;FALSE;FALSE;TRUE)
Tip: Brug F9 til at se disse resultater. Vælg blot et udtryk i formellinjen, og tryk på F9.
Matematikoperationen (multiplikation) transformerer de SANDE FALSKE værdier 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)
Efter multiplikation har vi et enkelt array som dette:
(0;0;1;0;0;0;0)
som føres ind i MATCH-funktionen som opslagsarray, med en opslagsværdi på 1:
MATCH(1,(0;0;1;0;0;0;0))
På dette tidspunkt er formlen en standard INDEX MATCH-formel. MATCH-funktionen returnerer 3 til INDEX:
=INDEX(E5:E11,3)
og INDEX returnerer et slutresultat på $ 17,00.
Array visualisering
Arrangementerne forklaret ovenfor kan være vanskelige at visualisere. Billedet nedenfor viser den grundlæggende idé. Kolonner B, C og D svarer til dataene i eksemplet. Kolonne F oprettes ved at multiplicere de tre kolonner sammen. Det er arrayet afleveret til MATCH.
Ikke-array version
Det er muligt at tilføje en anden INDEX til denne formel, undgå behovet for at indtaste som en matrixformel med kontrol + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))
INDEX-funktionen kan håndtere arrays indbygget, så den anden INDEX tilføjes kun for at "fange" den matrix, der er oprettet med den boolske logiske operation, og returnere den samme array igen til MATCH. For at gøre dette er INDEX konfigureret med nul rækker og en kolonne. Nul række trick får INDEX til at returnere kolonne 1 fra arrayet (som alligevel allerede er en kolonne).
Hvorfor vil du have den ikke-array-version? Nogle gange glemmer folk at indtaste en matrixformel med kontrol + shift + enter, og formlen returnerer et forkert resultat. Så en ikke-matrixformel er mere "skudsikker". Afvejningen er dog en mere kompleks formel.
Bemærk: I Excel 365 er det ikke nødvendigt at indtaste matrixformler på en særlig måde.
