Dynamic Arrays
 

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

Dynamic Arrays
 

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:E22H1,F2:F22))
to find the max
=MATCH(MAX(IF(E3:E23H2,F3:F23)),F:F,0)
to find the row



--
Don Guillett
SalesAid Software

"chaz" wrote in message
...

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