+ Reply to Thread
Results 1 to 4 of 4

Criteria Lookup based on Dates

  1. #1
    Tyson
    Guest

    Criteria Lookup based on Dates

    The "Current Plan" is the formula I'm trying to create:

    Starting Point:

    1 A B C D
    2 NAME PLAN Effective Date Current Plan
    3 ---- ---- -------------- ------------
    4 ID1 1 1/1/2005
    5 ID1 3 2/1/2005
    6 ID2 2 5/1/2004
    7 ID3 2 2/1/2005
    8 ID3 4 9/1/2005
    9
    10 AS OF DATE:
    11 3/1/2005


    What I'm trying to get to is the "Current Plan" based on the "AS OF
    DATE" I put in.


    Ending Point:

    1 A B C D
    2 NAME PLAN Effective Date Current Plan
    3 ---- ---- -------------- ------------
    4 ID1 1 1/1/2005
    5 ID1 3 2/1/2005 3
    6 ID2 2 5/1/2004 2
    7 ID3 2 2/1/2005
    8 ID3 4 9/1/2005 4
    9
    10 AS OF DATE:
    11 3/1/2005


    I figure I need some sort of between formual or a "<" but ">" formula.


    Can anyone help?

    Thanks.

    Tysone


  2. #2
    Bernard Liengme
    Guest

    Re: Criteria Lookup based on Dates

    You want D' to have B's value when C's data is ??????? (greater than, less
    than, equal to ) date in A11?
    Please use date values that tell us if you are using US (mm/dd/yyyy) or
    rest-of-world convention (dd/mm/yyyy)
    best wishes
    --
    Bernard V Liengme
    www.stfx.ca/people/bliengme
    remove caps from email

    "Tyson" <[email protected]> wrote in message
    news:[email protected]...
    > The "Current Plan" is the formula I'm trying to create:
    >
    > Starting Point:
    >
    > 1 A B C D
    > 2 NAME PLAN Effective Date Current Plan
    > 3 ---- ---- -------------- ------------
    > 4 ID1 1 1/1/2005
    > 5 ID1 3 2/1/2005
    > 6 ID2 2 5/1/2004
    > 7 ID3 2 2/1/2005
    > 8 ID3 4 9/1/2005
    > 9
    > 10 AS OF DATE:
    > 11 3/1/2005
    >
    >
    > What I'm trying to get to is the "Current Plan" based on the "AS OF
    > DATE" I put in.
    >
    >
    > Ending Point:
    >
    > 1 A B C D
    > 2 NAME PLAN Effective Date Current Plan
    > 3 ---- ---- -------------- ------------
    > 4 ID1 1 1/1/2005
    > 5 ID1 3 2/1/2005 3
    > 6 ID2 2 5/1/2004 2
    > 7 ID3 2 2/1/2005
    > 8 ID3 4 9/1/2005 4
    > 9
    > 10 AS OF DATE:
    > 11 3/1/2005
    >
    >
    > I figure I need some sort of between formual or a "<" but ">" formula.
    >
    >
    > Can anyone help?
    >
    > Thanks.
    >
    > Tysone
    >




  3. #3
    Tyson
    Guest

    Re: Criteria Lookup based on Dates

    Sorry... yes, US (mm/dd/yyyy).

    And you are Correct... I want "D" to have "B's" value. The catch is
    there will be multipule ID's and some of the ID's will repeat
    themselves but I only want to have one entry in "D" per unique ID.

    another Example:

    ID4 appreas 4 times in "A" with these dates 1/1/2005, 2/1/2005,
    3/1/2005, 4/1/2005. in "C"
    Cell "A11" = 3/1/2005
    I want the PLAN that is associated with ID4 on the date 3/1/2005

    Hopefully this all makes sense.

    Thanks again.

    Tysone


  4. #4
    Tyson
    Guest

    Re: Criteria Lookup based on Dates

    Sorry... yes, US (mm/dd/yyyy).

    And you are Correct... I want "D" to have "B's" value. The catch is
    there will be multipule ID's and some of the ID's will repeat
    themselves but I only want to have one entry in "D" per unique ID.

    another Example:

    ID4 appreas 4 times in "A" with these dates 1/1/2005, 2/1/2005,
    3/1/2005, 4/1/2005. in "C"
    Cell "A11" = 3/1/2005
    I want the PLAN that is associated with ID4 on the date 3/1/2005

    Hopefully this all makes sense.

    Thanks again.

    Tysone


+ 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