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/