Resumé
For at udføre en tovejs tilnærmet matchopslag med flere kriterier kan du bruge en matrixformel baseret på INDEX og MATCH med hjælp fra IF-funktionen til at anvende kriterier. I det viste eksempel er formlen i K8:
=INDEX(data,MATCH(K6,IF(material=K5,hardness),1),MATCH(K7,diameter,1))
hvor data (D6: H16), diameter (D5: H5), materiale (B6: B16) og hårdhed (C6: C16) er navngivne områder, der kun bruges af bekvemmelighed.
Bemærk: dette er en matrixformel og skal indtastes med Control + Shift + Enter
Forklaring
Målet er at slå op i en tilførselshastighed baseret på materiale, hårdhed og borediameter. Tilspænding værdier er i det navngivne område data (D6: H16).
Dette kan gøres med en tovejs INDEX- og MATCH-formel. Den ene MATCH-funktion udrækker række nummer (materiale og hårdhed), og den anden MATCH funktion finder kolonnenummeret (diameter). INDEX-funktionen returnerer det endelige resultat.
I det viste eksempel er formlen i K8:
=INDEX(data, MATCH(K6,IF(material=K5,hardness),1), // get row MATCH(K7,diameter,1)) // get column
(Linjeskift tilføjet kun for læsbarhed).
Det vanskelige er, at materiale og hårdhed skal håndteres sammen. Vi er nødt til at begrænse MATCH til hårdhedsværdierne for et givet materiale (Low Carbon Steel i det viste eksempel).
Vi kan gøre dette med IF-funktionen. I det væsentlige bruger vi IF til at "smide" irrelevante værdier, før vi ser efter en kamp.
detaljer
INDEX funktion er givet de navngivne range data (D6: H16) som for array. Den første MATCH-funktion udrækker række nummer:
MATCH(K6,IF(material=K5,hardness),1) // get row num
For at finde den rigtige række er vi nødt til at foretage en nøjagtig matchning på materiale og en omtrentlig match på hårdhed. Vi gør dette ved at bruge IF-funktionen til først at filtrere irrelevant hårdhed ud:
IF(material=K5,hardness) // filter
Vi tester alle værdierne i materiale (B6: B16) for at se, om de matcher værdien i K5 ("Low Carbon Steel"). I så fald overføres hårdhedsværdien. Hvis ikke, returnerer IF FALSE. Resultatet er en matrix som denne:
(FALSE;FALSE;FALSE;85;125;175;225;FALSE;FALSE;FALSE;FALSE)
Bemærk de eneste overlevende værdier er dem, der er forbundet med kulstofstål. De andre værdier er nu FALSE. Dette array returneres direkte til MATCH-funktionen som lookup_array.
Opslagsværdien for match kommer fra K6, som indeholder den givne hårdhed, 176. MATCH er konfigureret til omtrentlig matchning ved at indstille match_type til 1. Med disse indstillinger ignorerer MATCH FALSE-værdier og returnerer positionen for et nøjagtigt match eller den næste mindste værdi .
Bemærk: hårdhedsværdier skal sorteres i stigende rækkefølge for hvert materiale.
Med hårdhed angivet som 176 returnerer MATCH 6, leveret direkte til INDEX som række nummer. Vi kan nu omskrive den originale formel sådan:
=INDEX(data,6,MATCH(K7,diameter,1))
Den anden MATCH-formel finder det korrekte kolonnetal ved at udføre et omtrentligt match på diameteren:
MATCH(K7,diameter,1) // get column num
Bemærk: værdier i diameter D5: H5 skal sorteres i stigende rækkefølge.
Opslag værdi kommer fra K7 (0,75), og opslagsmatrixen er det navngivne område diameter (D5: H5).
Som før er MATCH indstillet til omtrentlig match ved at indstille match_type til 1.
Med diameter angivet som 0,75 returnerer MATCH 3, leveret direkte til INDEX-funktionen som kolonnenummer. Den oprindelige formel løser nu følgende:
=INDEX(data,6,3) // returns 0.015
INDEX returnerer et slutresultat på 0,015, værdien fra F11.
