Hi Mark,

Always be very prcise as to what you want the borders to be.
In your case, the second column seems to be superfluous.
With the value to be looked up in D2, and your table in A1:C6, use this
formula:

=VLOOKUP(D2,$A$2:$C$6,3)

--

Kind Regards,

Niek Otten

Microsoft MVP - Excel


"licksy " wrote in message
...
Hello.

I am trying to work out a method for rounding prices to specific price
points.

Say for example I enter a price into cell D1, I want the cell E1 to
search through the range to find what the appropriate rounded value
should be.
Take the following 3 columns - if the price is between A and B, I want
the value from C.

I can do this using a messy formula nesting IF and AND functions for a
small range, something like
=IF(AND($D$1<=B1,$D$1=A1),C1,IF(AND($D$1<=B2,$D$1 =A2),C2,"error"))
but I get stumped quickly because the range will have approx 150 rows.

A B C
1.08 1.34 1.29
1.35 1.55 1.49
1.56 1.80 1.79
1.81 2.07 1.99
2.08 2.34 2.29
etc.

I'd like this solution to be availabe for multiple workbooks but I'm
not familiar with macros very much.

Thanks and regards,
Mark.


---
Message posted from http://www.ExcelForum.com/