I am totally confused by your description but...

=LOOKUP(C7,B$7:B$2268,G$7:G$2268)
always returns the value in cell G2268.

In order for that formula to work correctly the range B7:B2268 *must* be
sorted in ascending order.

--
Biff
Microsoft Excel MVP


"Mal" wrote in message
...
I have a file of historical daily stock prices from this month back nine
years. First column is the date and next the data. Then the next three
columns are the dates for looking back 30, 60, and 90 days. I then tried
to
use LOOKUP to use the look back dates to look back and pick up the prices
for
30, 60, 90 days back to produce three new columns. That is, I use LOOKUP
to
use the 30, 60, 90 day look back dates to lookup the back date in the date
column and return the price on that date. The LOOKUP function returns the
same last (bottom) value in the column for all rows. That is,
=LOOKUP(C7,B$7:B$2268,G$7:G$2268) always returns the value in cell G2268.
Dates get older in down direction. C7 is the first reference date to be
found in B7:B2268 (exact or next older date) and result is in same row in
G7:G2268.
--
Mal