Forum Discussion
Solved
3 or more criteria lookup
Help needing to create a formula that would automatically filter data on E13 to E15 based 3 or more criteria as shown below and attached (excel is a mockup to keep actual work data private). cha...
The formula is:
=INDEX(($B$2:$E$4,$B$5:$E$7,$B$8:$E$10), MATCH(C13,VALUE(SUBSTITUTE(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),RIGHT(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),6),)),1),MATCH("Region "&D13,$B$1:$E$1,0),MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0))cheers
See the attached version. I unmerged the cells in column A and repeated the ages, and I split the weight ranges into two columns.
thank you! I suspected separating the weight range is a must, so will try and experiment with this.
The formula is:
=INDEX(($B$2:$E$4,$B$5:$E$7,$B$8:$E$10), MATCH(C13,VALUE(SUBSTITUTE(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),RIGHT(INDEX(($B$2:$B$4,$B$5:$B$7,$B$8:$B$10),,,MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0)),6),)),1),MATCH("Region "&D13,$B$1:$E$1,0),MATCH(B13,FILTER(A2:A10,(A2:A10>1)*1),0))cheers