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

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" wrote in message
oups.com...
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

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