VLOOKUP - Round Up to higher range?

[excelbetter]

I am trying to get the results from the table shown below whereby a input a random box weight (i.e. 24), and the vlookup gives me back the corresponding Max box weight, rate upcharge, and box rate from the "Legend" table.

In this case, if since my example has a weight of 24, I would like back the max box rate of 10, the rate upcharge of 25% and the box rate of $5.00.

Basically, the Weight Range is really the idea of any box between 1-6 lbs, 7 - 30 lbs, 31 - 159 lbs, etc. Not sure if I need to make the legend read 1-6, 7-31, etc.

Any thoughts are appreciated. Thank you.

 

[njimack]

As long as you don't mind sorting your table in descending order, I'd suggest INDEX and MATCH...

Excel Workbook
	A	B	C	D
1	Wt	Max	Upcharge	Box rate
2	99999	17	25%	5
3	1000	17	25%	5
4	159	17	25%	5
5	30	10	25%	5
6	6	6	20%	5
7
8	24	10	25%	5
Sheet1

 

[taurean]

If the above table is limited to 5 cases only then you can use Nested IFs as below:

Excel Workbook
	A	B	C	D
1	Weight Range	Max Box Weight	Rate Upcharge	Box Rate
2	6	6	20%	$5.00
3	30	10	25%	$5.00
4	159	17	25%	$5.00
5	1000	17	25%	$5.00
6	99999	17	25%	$5.00
7
8	Real Situation
9	Weight	Max Box Weight	Rate Upcharge	Box Rate
10	24	10	25.00%	$5.00
Sheet1

 

[Aladin Akyurek]

I am trying to get the results from the table shown below whereby a input a random box weight (i.e. 24), and the vlookup gives me back the corresponding Max box weight, rate upcharge, and box rate from the "Legend" table.

In this case, if since my example has a weight of 24, I would like back the max box rate of 10, the rate upcharge of 25% and the box rate of $5.00.

Basically, the Weight Range is really the idea of any box between 1-6 lbs, 7 - 30 lbs, 31 - 159 lbs, etc. Not sure if I need to make the legend read 1-6, 7-31, etc.

Any thoughts are appreciated. Thank you.

<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=410><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3584" width=101><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4295" width=121><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3754" width=106><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=101>Weight Range</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2818217 class=xl69 width=121>Max Box Weight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=106>Rate Upcharge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=82>Box Rate</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>20%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>159</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>99999</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=101> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=121> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=106> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19>Wight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Max Box Wight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Rate Upcharge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Box Rate</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68> </TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=106>0.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR></TBODY></TABLE>

A1:D6 houses the data, including the headers.

A10: 24 (a look up value of interest)

In B10 enter and copy across:

=INDEX(B2:B6,MATCH($A10,$A$2:$A$7,1)+(LOOKUP($A10,$A$2:$A$6) < $A10))

 

[taurean]

<TABLE style="WIDTH: 308pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=410><COLGROUP><COL style="WIDTH: 76pt; mso-width-source: userset; mso-width-alt: 3584" width=101><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4295" width=121><COL style="WIDTH: 79pt; mso-width-source: userset; mso-width-alt: 3754" width=106><COL style="WIDTH: 62pt; mso-width-source: userset; mso-width-alt: 2929" width=82><TBODY><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19 width=101>Weight Range</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 91pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" id=td_post_2818217 class=xl69 width=121>Max Box Weight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 79pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=106>Rate Upcharge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; WIDTH: 62pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 width=82>Box Rate</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>6</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>20%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>30</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>159</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>1000</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>99999</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>17</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 width=106>25%</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 height=19 width=101></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=121></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=106></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=82></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69 height=19>Wight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Max Box Wight</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Rate Upcharge</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl69>Box Rate</TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68 height=19></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68></TD></TR><TR style="HEIGHT: 14.4pt" height=19><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; WIDTH: 76pt; HEIGHT: 14.4pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 height=19 width=101>24</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 91pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=121>10</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 79pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=106>0.25</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; WIDTH: 62pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 width=82>5</TD></TR></TBODY></TABLE>

A1:D6 houses the data, including the headers.

A10: 24 (a look up value of interest)

In B10 enter and copy across:

=INDEX(B2:B6,MATCH($A10,$A$2:$A$7,1)+(LOOKUP($A10,$A$2:$A$6) < $A10))

The formula doesn't work when weight is 5 (less than 6). Maybe this formula:
=INDEX(B2:B6,IFERROR(MATCH($A10,$A$2:$A$6,1),0)+1)

 

[Aladin Akyurek]

The formula doesn't work when weight is 5 (less than 6). ...

The modification that would be needed depends what the OP would want to happen for weight values under 6.

 

[markmzz]

I think about a new table. If possible, then this can help you:

<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th><th>G</th><th>H</th><th>I</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: center;;">Weight</td><td style="text-align: center;;">Max Box Weigth</td><td style="text-align: center;;">Rate Upcharge</td><td style="text-align: center;;">Box Rate</td><td style="text-align: right;;"></td><td style="text-align: center;;">Weight Range</td><td style="text-align: center;;">Max Box Weigth</td><td style="text-align: center;;">Rate Upcharge</td><td style="text-align: center;;">Box Rate</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: center;;">24</td><td style="text-align: center;;">10</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">1</td><td style="text-align: center;;">6</td><td style="text-align: center;;">20%</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: center;;">6</td><td style="text-align: center;;">6</td><td style="text-align: center;;">20%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: center;;">7</td><td style="text-align: center;;">10</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;">31</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: center;;">30</td><td style="text-align: center;;">10</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td><td style="text-align: center;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: center;;">31</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style="text-align: center;;">159</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">8</td><td style="text-align: center;;">160</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">9</td><td style="text-align: center;;">1000</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">10</td><td style="text-align: center;;">1001</td><td style="text-align: center;;">17</td><td style="text-align: center;;">25%</td><td style="text-align: center;;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:3.6em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet1</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">B2</th><td style="text-align:left">=VLOOKUP(<font color="Blue">$A2,$F$2:$I$4,COLUMNS(<font color="Red">$A:B</font>)</font>)</td></tr></tbody></table></td></tr></table><br />
Markmzz

 

[rayankh]

[TABLE="width: 308"]
<tbody>[TR]
[TD="class: xl69, width: 101, bgcolor: transparent"]Weight Range[/TD]
[TD="class: xl69, width: 121, bgcolor: transparent"]Max Box Weight[/TD]
[TD="class: xl69, width: 106, bgcolor: transparent"]Rate Upcharge[/TD]
[TD="class: xl69, width: 82, bgcolor: transparent"]Box Rate[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]6[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]6[/TD]
[TD="class: xl67, width: 106, bgcolor: white"]20%[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]30[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]10[/TD]
[TD="class: xl67, width: 106, bgcolor: white"]25%[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]159[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]17[/TD]
[TD="class: xl67, width: 106, bgcolor: white"]25%[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]1000[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]17[/TD]
[TD="class: xl67, width: 106, bgcolor: white"]25%[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]99999[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]17[/TD]
[TD="class: xl67, width: 106, bgcolor: white"]25%[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
[TR]
[TD="class: xl65, width: 101, bgcolor: white"][/TD]
[TD="class: xl65, width: 121, bgcolor: white"][/TD]
[TD="class: xl65, width: 106, bgcolor: white"][/TD]
[TD="class: xl65, width: 82, bgcolor: white"][/TD]
[/TR]
[TR]
[TD="class: xl69, bgcolor: transparent"]Wight[/TD]
[TD="class: xl69, bgcolor: transparent"]Max Box Wight[/TD]
[TD="class: xl69, bgcolor: transparent"]Rate Upcharge[/TD]
[TD="class: xl69, bgcolor: transparent"]Box Rate[/TD]
[/TR]
[TR]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[TD="class: xl68, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="class: xl66, width: 101, bgcolor: white"]24[/TD]
[TD="class: xl66, width: 121, bgcolor: white"]10[/TD]
[TD="class: xl66, width: 106, bgcolor: white"]0.25[/TD]
[TD="class: xl66, width: 82, bgcolor: white"]5[/TD]
[/TR]
</tbody>[/TABLE]


A1:D6 houses the data, including the headers.

A10: 24 (a look up value of interest)

In B10 enter and copy across:

=INDEX(B2:B6,MATCH($A10,$A$2:$A$7,1)+(LOOKUP($A10,$A$2:$A$6) < $A10))

I attempted this for my own problem and it worked! Could you be so kind as to walk me through the logic of your formula? No need to explain the position locks... but what in the world does the + Lookup and the less than A10 do.. how does that part fix my problem?? <a10 do?="" how="" did="" that="" part="" solve="" my="" problem?!?!
Also, what sort of education have you went through to get this good?</a10>

 

[markmzz]

Another way:

Layout

[TABLE="width: 228"]
<tbody>[TR]
[TD="width: 82, bgcolor: transparent"]Weight Range[/TD]
[TD="width: 87, bgcolor: transparent"]Max Box Weight[/TD]
[TD="width: 82, bgcolor: transparent"]Rate Upcharge[/TD]
[TD="width: 52, bgcolor: transparent"]Box Rate[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: transparent, align: right"]20%[/TD]
[TD="bgcolor: transparent"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]30[/TD]
[TD="bgcolor: transparent, align: right"]10[/TD]
[TD="bgcolor: transparent, align: right"]25%[/TD]
[TD="bgcolor: transparent"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]159[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]25%[/TD]
[TD="bgcolor: transparent"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1000[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]25%[/TD]
[TD="bgcolor: transparent"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]99999[/TD]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: transparent, align: right"]25%[/TD]
[TD="bgcolor: transparent"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]*[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Real Situation[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]Weight[/TD]
[TD="bgcolor: transparent"]Max Box Weight[/TD]
[TD="bgcolor: transparent"]Rate Upcharge[/TD]
[TD="bgcolor: transparent"]Box Rate[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]0,9[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: yellow, align: right"]6[/TD]
[TD="bgcolor: yellow, align: right"]0,2[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: yellow, align: right"]6[/TD]
[TD="bgcolor: yellow, align: right"]0,2[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6[/TD]
[TD="bgcolor: yellow, align: right"]6[/TD]
[TD="bgcolor: yellow, align: right"]0,2[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]6,01[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]24[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]25[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]29[/TD]
[TD="bgcolor: yellow, align: right"]10[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]33[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]150[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]267[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]384[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]501[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]618[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]735[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]852[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]969[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1086[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]1203[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]9900[/TD]
[TD="bgcolor: yellow, align: right"]17[/TD]
[TD="bgcolor: yellow, align: right"]0,25[/TD]
[TD="bgcolor: yellow"]$5.00[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]18597[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]27294[/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[TD="bgcolor: yellow"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]****************[/TD]
[TD="bgcolor: transparent"]***************[/TD]
[TD="bgcolor: transparent"]*********[/TD]
[/TR]
</tbody>[/TABLE]


Formula

In B10

=IF(($A10<1)+($A10>9999),"",INDEX(B$2:B$6,MATCH(0,INDEX(-($A$2:$A$6<$A10),),0)))

Markmzz

 

[Aladin Akyurek]

I attempted this for my own problem and it worked! Could you be so kind as to walk me through the logic of your formula? No need to explain the position locks... but what in the world does the + Lookup and the less than A10 do.. how does that part fix my problem??<a10 do?="" how="" did="" that="" part="" solve="" my="" problem?!?!

</a10>

Let's have a table in A2:B4 like below:

[TABLE="class: grid, width: 100"]
<colgroup><col style="mso-width-source:userset;mso-width-alt:1280;width:27pt" width="36"> <col style="mso-width-source:userset;mso-width-alt:1223;width:26pt" width="34"> </colgroup><tbody>[TR]
[TD="width: 36, align: right"]3[/TD]
[TD="width: 34"]YAD
[/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]VAD
[/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]XAD
[/TD]
[/TR]
</tbody>[/TABLE]

and a look up value (LV) of 5 in A10. The desired value is VAD, corresponding to 8.

We would get:

INDEX($B$2:$B$4,MATCH($A10,$A$2:$A$4,1)+(LOOKUP($A10,$A$2:$A$4) < $A10))

==>

INDEX($B$2:$B$4,MATCH(5,$A$2:$A$4,1)+(LOOKUP(5,$A$2:$A$4) < 5))

==>

INDEX($B$2:$B$4,1+(LOOKUP(5,$A$2:$A$4) < 5))

==>

INDEX($B$2:$B$4,1+(3 < 5))

Since LOOKUP effects by default an approximate match, we would get either LV itself if available in the reference or the largest value that is less than LV. Here 5 --> 3.

==>

INDEX($B$2:$B$4,1+(TRUE))

==>

INDEX($B$2:$B$4,1+1)

The operator + causes the coercion of TRUE to 1 and FALSE to 0 (The numeric equivalents for TRUE and FALSE are respectively 1 and 0 in Excel.).

==>

INDEX($B$2:$B$4,2)

==>

VAD

If we set LV to 12, the inequality test with LOOKUP would yield 0 and the MATCH output would stay 3 and the end result would be XAD as intended.

Hope this helps.

Also, what sort of education have you went through to get this good?

Exploring how the functions behave helps a lot along with studying example solutions and trying to craft solutions for the common problems. A formal background is not always but often supportive.