Dette er en mer avansert formel. For grunnleggende, se Hvordan bruke INDEKS og KAMP.
Vanligvis, en INDEKS MATCH formelen er konfigurert med KAMPEN sett å se gjennom et ett-kolonnen for utvalg og gir en kamp basert på gitte kriterier. Uten å slå sammen verdier i en hjelper-kolonnen, eller i selve formelen, det er ingen måte å levere mer enn ett kriterium.,
Denne formelen fungerer omgå dette problemet ved å bruke boolsk logikk for å opprette en matrise av enere og nuller til å representere matchende rader alle 3 kriterier, så bruker KAMP for å matche den første 1 funnet. Den midlertidige utvalg av enere og nuller er generert med denne kodebiten:
(H5=B5:B11)*(H6=C5:C11)*(H7=D5:D11)Her har vi sammenligne element i H5 mot alle elementer, størrelsen på H6 mot alle størrelser, og fargen i H7 mot alle farger. Det første resultatet er tre rekker av TRUE/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.
math drift (multiplikasjon) forvandler TRUE FALSE verdier å 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, vi har et enkelt utvalg som dette:
{0;0;1;0;0;0;0}som er matet inn i KAMPEN funksjon som oppslag array, med et oppslag verdi av 1:
MATCH(1,{0;0;1;0;0;0;0})På dette punktet, formelen er en standard INDEKS MATCH formel., KAMPEN funksjonen returnerer 3 til INDEX:
=INDEX(E5:E11,3)og INDEKSEN gir et endelig resultat av $17.00.
Array visualisering
matriser forklart ovenfor, kan det være vanskelig å visualisere. Bildet nedenfor viser den grunnleggende ideen. Kolonnene B, C, og D svarer til data i eksemplet. Kolonne F er opprettet ved å multiplisere de tre kolonnene sammen. Det er matrisen levert av å MATCHE.,
Ikke-array-versjon
Det er mulig å legge til en annen INDEKS til denne formelen, slik at du slipper å taste inn som en matrise formel med control + shift + enter:
=INDEX(rng1,MATCH(1,INDEX((A1=rng2)*(B1=rng3)*(C1=rng4),0,1),0))The INDEX-funksjonen kan håndtere matriser problemfritt, så den andre INDEKSEN er lagt til bare å «fange» den utvalg som er opprettet med boolsk logikk operasjonen og gå tilbake samme rekke igjen til KAMP. For å gjøre dette, INDEKS er konfigurert med null rad og en kolonne. Null rad lure årsaker INDEKS for å gå tilbake kolonne 1 i tabellen (som er allerede en kolonne uansett).,
Hvorfor ville du vil at den ikke-array versjonen? Noen ganger, folk glemmer å skrive inn en matrise formel med control + shift + enter, og formelen returnerer en feil resultat. Så, en ikke-matriseformel mer «skuddsikkert». Men bakdelen er en mer komplisert formel.
Merk: I Excel 365, det er ikke nødvendig å skrive inn matrisen formler på en spesiell måte.