Sammendrag
For å utføre en toveis omtrentlig kampoppslag med flere kriterier, kan du bruke en matriseformel basert på INDEKS og MATCH, med hjelp fra IF-funksjonen til å anvende kriterier. I eksemplet vist er formelen i K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
der data (D6: H16), diameter (D5: H5), materiale (B6: B16) og hardhet (C6: C16) er kalt områder som kun brukes for enkelhets skyld.
Merk: dette er en matriseformel og må angis med Control + Shift + Enter
Forklaring
Målet er å slå opp en matehastighet basert på materiale, hardhet og borediameter. Matehastighet verdier er i de nevnte avstandsdata (D6: H16).
Dette kan gjøres med en toveis INDEX- og MATCH-formel. Den ene MATCH-funksjonen regner ut radnummeret (materiale og hardhet), og den andre MATCH-funksjonen finner kolonnetallet (diameteren). INDEX-funksjonen returnerer det endelige resultatet.
I eksemplet vist er formelen i K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Linjeskift lagt til kun for lesbarhet).
Det vanskelige er at materiale og hardhet må håndteres sammen. Vi må begrense MATCH til hardhetsverdiene for et gitt materiale (Low Carbon Steel i eksemplet vist).
Vi kan gjøre dette med IF-funksjonen. I hovedsak bruker vi IF til å "kaste" irrelevante verdier før vi ser etter en kamp.
Detaljer
INDEX funksjon er gitt de nevnte avstandsdata (D6: H16) som for matrisen. Den første MATCH-funksjonen regner ut radnummeret:
MATCH(K6,IF(material=K5,hardness),1) // get row num
For å finne riktig rad, må vi gjøre en nøyaktig samsvar på materialet, og en omtrentlig match på hardheten. Vi gjør dette ved å bruke IF-funksjonen til å først filtrere ut irrelevant hardhet:
IF(material=K5,hardness) // filter
Vi tester alle verdiene i materialet (B6: B16) for å se om de samsvarer med verdien i K5 ("Low Carbon Steel"). I så fall blir hardhetsverdien passert. Hvis ikke returnerer IF FALSE. Resultatet er en matrise som dette:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Legg merke til at de eneste gjenlevende verdiene er de som er knyttet til karbonfattig stål. De andre verdiene er nå FALSE. Denne matrisen returneres direkte til MATCH-funksjonen som oppslagsarray.
Oppslagsverdien for samsvar kommer fra K6, som inneholder den oppgitte hardheten, 176. MATCH er konfigurert for tilnærmet samsvar ved å sette match_type til 1. Med disse innstillingene ignorerer MATCH FALSE verdier og returnerer posisjonen til et eksakt samsvar eller den neste minste verdien .
Merk: hardhetsverdiene må sorteres i stigende rekkefølge for hvert materiale.
Med hardhet gitt som 176, returnerer MATCH 6, levert direkte til INDEX som radnummer. Vi kan nå omskrive den opprinnelige formelen slik:
=INDEX(data,6,MATCH(K7,diameter,1))
Den andre MATCH-formelen finner riktig kolonnetall ved å utføre en omtrentlig kamp på diameter:
MATCH(K7,diameter,1) // get column num
Merk: verdiene i diameter D5: H5 må sorteres i stigende rekkefølge.
Oppslags verdien kommer fra K7 (0,75), og den matrise er det navngitte området diameter (D5: H5).
Som tidligere er MATCH satt til omtrentlig kamp ved å sette match_type til 1.
Med diameter gitt som 0,75 returnerer MATCH 3, levert direkte til INDEX-funksjonen som kolonnenummer. Den opprinnelige formelen løser nå å:
=INDEX(data,6,3) // returns 0.015
INDEX returnerer et endelig resultat på 0,015, verdien fra F11.
