Criteria Lookup based on Dates

  • Thread starter Thread starter Tyson
  • Start date Start date
T

Tyson

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
 
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
 
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
 
Back
Top