Forum Discussion

pebbles1221's avatar
pebbles1221
Copper Contributor
Aug 19, 2019
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...
excel
Formulas and Functions
  • Haytham Amairah's avatar
    Haytham Amairah
    Aug 19, 2019

    pebbles1221

     

    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

SergeiBaklan's avatar
SergeiBaklan
Diamond Contributor
Aug 19, 2019

pebbles1221 

Couple of more variants

=INDEX($F$4:$L$9,
    IFERROR(AGGREGATE(15,6,1/($E$4:$E$9>=$B$3)*ROW($E$4:$E$9)-ROW($E$3),1),6),
    IFERROR(AGGREGATE(15,6,1/($F$2:$L$2>=$B$2)*COLUMN($F$2:$L$2)-COLUMN($E$2),1),7))

and for this specific range

=75+
   (B2>=60)*((CEILING.MATH(B2/60*2)-2)*25+5*(B2>60)+5*(B2>210))+
   (B3>=90)*(MIN(CEILING.MATH(B3/90*3),8)-3)*15

Resources