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

Tysone

 

[RagDyer]

Why does your "ending point" example have a 3 in D5?

 

[Tyson]

D5 is 3 because the "AS OF DATE" is 3/1/2005 and on 2/1/2005 ID1's
current plan was changed to a 3 from a 1.

I did make one error though on the matrix, it should read:

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 2
8 ID3 4 9/1/2005


I changed ID3's plan to a 2 because 9/1/2005 hadn't been reached as of
3/1/2005 (the AS OF DATE)

Sorry, and thanks.

Tyson

 

[Max]

Not very sure, but here's 2 interps to play with ..

Sample constructs at:
http://www.savefile.com/files/6556941
Criteria_Lookup_based_on_Dates_Tyson_gen.xls

Interp1
----------
Assuming the table below is in A1:C6

NAME PLAN Effective Date
ID1 1 01-Jan-05
ID1 3 01-Mar-05
ID2 2 01-Feb-05
ID3 2 01-Mar-05
ID3 4 01-Mar-05

and the "AS OF DATE:" below is in A11
01-Mar-05

(Date formats used above is to remove any ambiguity)

Put in D2, copy down: =IF(C2=$A$11,B2,"")

Interp2
-----------
Assuming the same table as interp1 (in A1:C6)
with the "AS OF DATE:" placed in G2: 01-Mar-05

Put in D2: =IF(COUNTIF($A$2:A2,A2)>1,"",ROW())

Put in E2:
=IF(ISERROR(SMALL(D,ROW(A1))),"",
INDEX(A:A,MATCH(SMALL(D,ROW(A1)),D,0)))

Put in F2, array-enter (press CTRL+SHIFT+ENTER):
=IF(ISNA(MATCH(1,($A$2:$A$6=E2)*($C$2:$C$6=$G$2),0)),"",
INDEX($B$2:$B$6,MATCH(1,($A$2:$A$6=E2)*($C$2:$C$6=$G$2),0)))

Select D2:F2, copy down to F6

Col E returns all the unique IDs, col F returns the specific Plan# (if any)
for the ID with date equal to the "AS OF DATE:" placed in G2: 01-Mar-05. If
there are any duplicate "ID-Date"'s satisfying the criteria, only the 1st
matched Plan# will be returned.

Adapt to suit ..

 

[Tyson]

Thanks for all the help!

Tyson

 

[Max]

You're welcome !