VLOOKUP confused

[lox1985]

Hey gurus,

I'm trying to find a workaround for a VLOOKUP i have in place. Right now, I have a vlookup that finds the rate according to the price that is set. I have a range of about 15 rates in one column and pricing associated with each rate in the next column. Everything works great as long as the pricing is in Low to High order. The problem comes in when Higher rate will have lower pricing and because of my my pick set in place and the way other formulas are written, VLOOKUP would grab higher rate when you can get lower rate with same pricing to it.

This a formula I use right now:

=VLOOKUP(Chosen Price,Range of Prices and Rates,4)

My ultimate goal is to be able for a formula to destinguish between prices and rates and pick lower rate with pricing as close to my Chosen Price as possible.
Example of data (as you can see it flows nicely from Low to High for Prices), if my chosen price is 97.000, my VLOOKUP will give me a rate of 4.625


<TABLE style="WIDTH: 276pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=368 border=0 x:str><COLGROUP><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 2730" width=96><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2503" width=88><COL style="WIDTH: 72pt; mso-width-source: userset; mso-width-alt: 2730" width=96><COL style="WIDTH: 66pt; mso-width-source: userset; mso-width-alt: 2503" width=88><TBODY><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" width=96 height=16>N/A</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=88>N/A</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 72pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=96>N/A</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 66pt; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" width=88 x:num>4.250</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="95.709000000000003">95.709</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="95.516000000000005">95.516</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="94.888000000000005">94.888</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="4.375">4.375</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="96.236000000000004">96.236</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="96.043000000000006">96.043</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="95.415000000000006">95.415</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num>4.500</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="96.617000000000004">96.617</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="96.424000000000007">96.424</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="95.796000000000006">95.796</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="4.625">4.625</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="97.498000000000005">97.498</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="97.289000000000001">97.289</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="96.631">96.631</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num>4.750</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="98.406000000000006">98.406</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="98.197999999999993">98.198</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="97.539000000000001">97.539</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="4.875">4.875</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="98.772999999999996">98.773</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="98.564999999999998">98.565</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="97.906000000000006">97.906</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num>5.000</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="99.138999999999996">99.139</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num>98.930</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="98.272000000000006">98.272</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="5.125">5.125</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="99.503">99.503</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="99.278999999999996">99.279</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="98.620999999999995">98.621</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num>5.250</TD></TR><TR style="HEIGHT: 12pt; mso-height-source: userset" height=16><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12pt; BACKGROUND-COLOR: white" height=16 x:num="100.16200000000001">100.162</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="99.938000000000002">99.938</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="99.281000000000006">99.281</TD><TD class=xl33 style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; BORDER-BOTTOM: #d4d0c8; BACKGROUND-COLOR: white" x:num="5.375">5.375</TD></TR></TBODY></TABLE>

In some instances, I would see a Price closer to 97 at a higher rate and then it would take higher rate over a lower rate.

I hope that is clear, please let me know if more clarification is needed.

Thank you

 

[Domenic]

Can you provide the expected results for the following lookup values?

94
94.915
95.6
97
98.6
101

Also, can N/A be replaced with 0 in the table?

 

[lox1985]

Dominic, thanks for the reply. I can't replace N/A with a 0, it has to stay that way. Out of your example, I would get a result of a rate next to 95.6.

Thanks,

 

[lox1985]

I'm sorry, in my example I was looking for pricing of 97. Going with that, my end result from your data would be rate next to 97

 

[Domenic]

Sorry, it looks like I probably wasn't clear in my last post. The numbers I gave you were sample prices. I wanted the expected results for each of them. This would have helped in my understanding of your question. In any case, maybe this is what you're looking for...

=INDEX($D$1:$D$10,MAX(IF(ISNUMBER($A$1:$C$10),IF($A$1:$C$10=MAX(IF(ISNUMBER($A$1:$C$10),IF($A$1:$C$10<=F1,$A$1:$C$10))),ROW($A$1:$C$10)-ROW($A$1)+1))))

...where A1:D10 contains the data/lookup table, and F1 contains the chosen price/lookup value. Note that this formula needs to be confirmed with CONTROL+SHIFT+ENTER, not just ENTER.

Basically, the formula finds the largest value in A1:D10 that is less than or equal to the value in F1, and returns the corresponding value in D1:D10. If the price less than 94.888, the formula returns 4.25. If the price is greater than 101.62, the formula returns 5.375.

 

[lox1985]

Thanks Domenic, I'll give it a shot on Monday.

 

[Domenic]

Make that...

=INDEX($D$1:$D$10,MAX(IF($A$1:$C$10=MAX(IF($A$1:$C$10<=F1,$A$1:$C$10)),ROW($A$1:$C$10)-ROW($A$1)+1)))

...confirmed with CONTROL+SHIFT+ENTER.