Criteria Lookup based on Dates

[Tyson]

I know I know, this was already posted in a nother group, but no one
over there could help me, so I'm hoping someone over here will be able
to help me that didn't see it on the group.

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

Dates are all US (mm/dd/yyyy).

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.

I want "D" (Current Plan) to have "B's" (Plan) 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

Tyson