Forum Discussion
Solved
Help with Excel Formula and table lookup
I need help with a formulae in the "Answer" cell that checks the Width Value and the Length Value against the table of values to the right. It should then bring back the correct value. I have added e...
Hi,
Try this formula:
=INDEX(K4:Q9,
IFNA(IF(OR(ISNUMBER(MATCH(B3,J4:J9,0)),B3>MAX(J4:J9)),MATCH(B3,J4:J9,1),MATCH(B3,J4:J9,1)+1),1),
IFNA(IF(OR(ISNUMBER(MATCH(B2,K2:Q2,0)),B2>MAX(K2:Q2)),MATCH(B2,K2:Q2,1),MATCH(B2,K2:Q2,1)+1),1))Regards
Hello pebbles1221,
Assuming your answer is in cell B5, try this:
=INDEX($K$4:$Q$9,MATCH(IF(CEILING.MATH($B$3,30)<90,90,IF(CEILING.MATH($B$3,30)>240,240,CEILING.MATH($B$3,30))),$J$4:$J$9,0),MATCH(IF(CEILING.MATH($B$2,30)<60,60,IF(CEILING.MATH($B$2,30)>240,240,CEILING.MATH($B$2,30))),$K$2:$Q$2,0))
Thank you very much for your support
- My pleasure!