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

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

Assume source data in B1:C1 down,
with real dates, chronologic (ie ascending), in B1 down,
corresponding data in C1 down

In D7 down you have listed real dates with which to look back 30 days
Place in say G7:
=INDEX(C:C,MATCH(DATE(YEAR(D7),MONTH(D7),DAY(D7)-30),B:B,1))
Copy down

And in E7 down you have listed real dates with which to look back 60 days
Place in say H7:
=INDEX(C:C,MATCH(DATE(YEAR(E7),MONTH(E7),DAY(E7)-60),B:B,1))
Copy down

And in F7 down you have listed real dates with which to look back 90 days
Place in say I7:
=INDEX(C:C,MATCH(DATE(YEAR(F7),MONTH(F7),DAY(F7)-90),B:B,1))
Copy down
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik
---
"Mal" wrote:
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.