Dynamic Reference in Offset Function?

[blogan418]

Hi - I'm trying to set a dynamic starting reference for the OFFSET function. The purpose of the calculation is return (in cell E17) the maximum price over the next 10 days starting from a certain dynamic date.

In Cell D17, I've been able to create a formula which determines the date (from Column A) on which the search for maximum price must begin.

What I'd like to do is take that Date (in column A) and begin a search for the maximum price over the next 10 days (in Column B).

The base of the formula I've tried to use is

=MAX(OFFSET(???,-1,0,-10,1))

What I can't determine is what to put in the initial reference given that the date in D17 can change.

Book1.xlsx
	A	B	C	D	E
1	Date	Price	Trade Trigger Price	First Date in which Price is Greater than Trade Trigger Price	Maximum Price 10 Days After the Date of the Trade Trigger
2	12/12/2008	387.53
3	12/11/2008	402.64
4	12/10/2008	419.85
5	12/9/2008	423.31
6	12/8/2008	410.07
7	12/5/2008	402.32
8	12/4/2008	420.70
9	12/3/2008	400.05
10	12/2/2008	379.70
11	12/1/2008	395.00
12	11/28/2008	395.90
13	11/26/2008	384.56
14	11/25/2008	379.89
15	11/24/2008	351.23
16	11/21/2008	350.00
17	11/20/2008	369.92	371.01	11/25/2008
Sheet1

Cell Formulas
Range	Formula
D17	D17	=LOOKUP(2,1/(OFFSET(B17,-1,0,-10,1)>=C17),OFFSET(A17,-1,0,-10,1))

 

[Fluff]

How about

=MAX(INDEX(B2:B17,MATCH(D17,A2:A17,0)):INDEX(B2:B17,MATCH(D17,A2:A17,0)-10))

 

[blogan418]

How about

=MAX(INDEX(B2:B17,MATCH(D17,A2:A17,0)):INDEX(B2:B17,MATCH(D17,A2:A17,0)-10))

Works perfect. Thanks again!!

 

[Fluff]

You're welcome & thanks for the feedback.