Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Rank items, select one start date, have remaining dates follow based on rank | Excel Discussion (Misc queries) | |||
Lookup: 2 criteria | Excel Discussion (Misc queries) | |||
formula to calculate a column based on dates in a different colum. | Excel Worksheet Functions | |||
conditional formula - based on 2 separate criteria | Excel Worksheet Functions | |||
Is it possible to use the sumif function based on the criteria of. | Excel Worksheet Functions |