Complex VLookup Formula

[simer]

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

 

[T. Valko]

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)

 

[Pete_UK]

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