+ Reply to Thread
Results 1 to 2 of 2

Dynamic Arrays

  1. #1
    Registered User
    Join Date
    05-19-2006
    Posts
    7

    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

  2. #2
    Don Guillett
    Guest

    Re: 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: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
    dguillett1@austin.rr.com
    "chaz" <chaz.288dky_1148337900.49@excelforum-nospam.com> wrote in message
    news:chaz.288dky_1148337900.49@excelforum-nospam.com...
    >
    > 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
    >




+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1