• 461K Members
• 5,765 Online
• 559K Conversations
SOLVED

New Contributor

# 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

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

# Re: Help with Excel Formula and table lookup

Hello @pebbles1221,

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

Solution

# Re: Help with Excel Formula and table lookup

@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

# Re: Help with Excel Formula and table lookup

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

# Re: Help with Excel Formula and table lookup

Thank you very much for your support

My pleasure!

# Re: Help with Excel Formula and table lookup

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``````
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies
How to Prevent Teams from Auto-Launch
chenrylee in Microsoft Teams on
29 Replies
Security Community Webinars
Valon_Kolica in Security, Privacy & Compliance on
13 Replies