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 examples below. I hope someone can assist.

 

Examples

If Width was 60 and Length was 158, the answer would be 120
If Width was 48 and Length was 150, the answer would be 105
If Width was 48 and Length was 60, the answer would be 75 because it is up to and including 60 for the width, up to and including 90 for the length
If Width was 91 and Length was 245, the answer would be 205

 

Many thanks in advance.

 

                  
Width68       WIDTH6090120150180210240 
Length158       Length        
         9075100130160190220260 
Answer145       12090115145175205235275 
         150105130160190220250290 
         180120145175205235265305 
         210135160190220250280320 
         240150175205235265295335 
                  
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

6 Replies

  • 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
  • Haytham Amairah's avatar
    Haytham Amairah
    Silver Contributor
    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

    • pebbles1221's avatar
      pebbles1221
      Copper Contributor
      Aug 19, 2019

      I cannot thank you enough. You are amazingly talented. Thank you. This is exactly what I was looking for.

  • PReagan's avatar
    PReagan
    Bronze Contributor
    Aug 19, 2019

    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))

Resources