Dynamic Arrays

C

chaz

Is there a way to set array bounds dynamically for functions such as
INDEX, VLOOKUP, etc?

I need to look up data within a specified timeframe.
The data is time-ordered stock market information.
The question I need to answer is:
"when did the XYZ stock's value first exceed $20 after Jan-1-2005"
I can use the INDEX and MATCH functions to find the first time the
stock exceeds $20, but I can't find a way to have the search start at a
row for a given date.
The data would be a series of rows of the form:

date value p/e ...

Regards,
Chaz
 
D

Don Guillett

try this where e is the dates and f is the quote and h1 has the date desired
This is an array formula so it needs to be entered with ctrl+shift+enter

=MAX(IF(E2:E22>H1,F2:F22))
to find the max
=MATCH(MAX(IF(E3:E23>H2,F3:F23)),F:F,0)
to find the row
 
Top