SOLVED

Help with Excel Formula and table lookup

Copper Contributor

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 
                  
6 Replies

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

best response confirmed by pebbles1221 (Copper Contributor)
Solution

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

2019-08-19_18-47-13.png

 

Regards

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

Thank you very much for your support
My pleasure!

@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
1 best response

Accepted Solutions
best response confirmed by pebbles1221 (Copper Contributor)
Solution

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

2019-08-19_18-47-13.png

 

Regards

View solution in original post