Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex VLookup Formula
I have two worksheets:
The first sheet contains date ranges that relate to specific "Rounds": Start End Round 3-Apr-10 15-Apr-10 1 20-Mar-10 2-Apr-10 2 6-Mar-10 19-Mar-10 3 20-Feb-10 5-Mar-10 4 The second worksheet contains a list of tasks which include a start date and an end date. I would like to set up a formula that would look at the end date for each task, and automatically assign the proper "Round" number in a separate column, using the data from the first sheet. So for example, if a task has an end date of 5-Apr-10, the formula will insert a 1 in the Round column in that specific row. HELP!!! |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex VLookup Formula
It appears that no date intervals will overlap so try this:
A2 = some date =SUMPRODUCT(--(A2=Sheet1!A2:A5),--(A2<=Sheet1!B2:B5),Sheet1!C2:C5) =SUMPRODUCT(--(A2=Start),--(A2<=End),Round) -- Biff Microsoft Excel MVP "simer" wrote in message ... I have two worksheets: The first sheet contains date ranges that relate to specific "Rounds": Start End Round 3-Apr-10 15-Apr-10 1 20-Mar-10 2-Apr-10 2 6-Mar-10 19-Mar-10 3 20-Feb-10 5-Mar-10 4 The second worksheet contains a list of tasks which include a start date and an end date. I would like to set up a formula that would look at the end date for each task, and automatically assign the proper "Round" number in a separate column, using the data from the first sheet. So for example, if a task has an end date of 5-Apr-10, the formula will insert a 1 in the Round column in that specific row. HELP!!! |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Complex VLookup Formula
Can you sort the data table on Start or End Date, so that it looks
like this: Start End Round 20-Feb-10 5-Mar-10 4 6-Mar-10 19-Mar-10 3 20-Mar-10 2-Apr-10 2 3-Apr-10 15-Apr-10 1 ? If so, then suppose your end dates are in column E of sheet2, starting on row 2. Use this formula to get the Round: =VLOOKUP(E2,Sheet1!B$2:C$5,2) Hope this helps. Pete On Aug 20, 12:16*am, simer wrote: I have two worksheets: The first sheet contains date ranges that relate to specific "Rounds": Start * * * * End * * * * * Round 3-Apr-10 * * * * * 15-Apr-10 * * *1 20-Mar-10 * * * * *2-Apr-10 * * * * * * 2 6-Mar-10 * * * * * 19-Mar-10 * * *3 20-Feb-10 * * * * *5-Mar-10 * * * *4 The second worksheet contains a list of tasks which include a start date and an end date. I would like to set up a formula that would look at the end date for each task, and automatically assign the proper "Round" number in a separate column, using the data from the first sheet. So for example, if a task has an end date of 5-Apr-10, the formula will insert a 1 in the Round column in that specific row. HELP!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
complex vlookup function - possible? help! | Excel Worksheet Functions | |||
Complex Vlookup Table | Excel Discussion (Misc queries) | |||
Help with complex VLOOKUP | Excel Discussion (Misc queries) | |||
Help with complex VLOOKUP | Excel Worksheet Functions | |||
Complex VLOOKUP | Excel Discussion (Misc queries) |