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!!!

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!!!

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!!!