08-19-2019 07:45 AM
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.
Width | 68 | WIDTH | 60 | 90 | 120 | 150 | 180 | 210 | 240 | ||||||||
Length | 158 | Length | |||||||||||||||
90 | 75 | 100 | 130 | 160 | 190 | 220 | 260 | ||||||||||
Answer | 145 | 120 | 90 | 115 | 145 | 175 | 205 | 235 | 275 | ||||||||
150 | 105 | 130 | 160 | 190 | 220 | 250 | 290 | ||||||||||
180 | 120 | 145 | 175 | 205 | 235 | 265 | 305 | ||||||||||
210 | 135 | 160 | 190 | 220 | 250 | 280 | 320 | ||||||||||
240 | 150 | 175 | 205 | 235 | 265 | 295 | 335 | ||||||||||
08-19-2019 08:44 AM - edited 08-19-2019 08:52 AM
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))
08-19-2019 08:48 AM
Solution
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
08-19-2019 09:16 AM
I cannot thank you enough. You are amazingly talented. Thank you. This is exactly what I was looking for.
08-19-2019 09:17 AM
08-19-2019 03:11 PM
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