Dynamic Arrays

[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

[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



--
Don Guillett
SalesAid Software
[email protected]
"chaz" <[email protected]> wrote in message
news:[email protected]...
>
> 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
>
>
> --
> chaz
> ------------------------------------------------------------------------
> chaz's Profile:
> http://www.excelforum.com/member.php...o&userid=34616
> View this thread: http://www.excelforum.com/showthread...hreadid=544486
>