How do I find a match for three variables to determine the result?

[SykesvilleJim]

I have a table called “Dates†with data that looks like the following:



A B C A

3 Spin Start date End date Month of Spin

4 10 1/1/09 0:00 1/31/09 23:59 1

5 10 2/1/09 0:00 2/28/09 23:59 2

6 10 3/1/09 0:00 3/31/09 23:59 3

7 10 4/1/09 0:00 4/30/09 23:59 4

8 10 5/1/09 0:00 5/31/09 23:59 5

9 10 6/1/09 0:00 6/30/09 23:59 6

10 11 3/1/09 0:00 3/31/09 23:59 1

11 11 4/1/09 0:00 4/30/09 23:59 2

12 11 5/1/09 0:00 5/31/09 23:59 3

13 11 6/1/09 0:00 6/30/09 23:59 4

14 11 7/1/09 0:00 7/31/09 23:59 5

15 11 8/1/09 0:00 8/31/09 23:59 6

16 12 5/1/09 0:00 5/31/09 23:59 1

17 12 6/1/09 0:00 6/30/09 23:59 2

18 12 7/1/09 0:00 7/31/09 23:59 3

19 12 8/1/09 0:00 8/31/09 23:59 4

20 12 9/1/09 0:00 9/30/09 23:59 5

21 12 10/1/09 0:00 10/31/09 23:59 6


I have another sheet (Actuals) that provides a Spin number (Row A – “Spinâ€)
and a Date the report was generated (Row B – “Date of reportâ€).



A B C

3 Spin Date of Report Month of Spin

4 10 1/15/2009

5 11 6/3/2009

6 10 5/5/2009

7 11 7/17/2009

8 11 4/25/2009

9 12 1/15/2009

10 12 9/15/2009

11 11 4/21/2009

12 12 5/23/2009

13 11 8/9/2009


How do I find the Month of the spin associated with the “Actuals†data by
validating the Spin number and date match on the “Dates†Spreadsheet??

Help!! Thanks much!!

 

[ker_01]

If each value pair only occurs once ( no repeats), you might try using
sumproduct; something like (aircode):

[on sheet2!C2, where you want the value to show up for that row]
=sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=B2)*1, (Sheet2!D))

so if the first two conditions evaluate to true, they get a value of one,
which is multiplied against the 'month' value in column D to return just that
value.

However, it also appears that your original values are only the first of
each month, with no hours/minutes, and the comparison values can be any day,
along with hours/minutes. There are a few ways to do this, so I'll let you
pick your preference. For me I'd probably extract the year/month and create a
simplified date- something like:
=DATE(YEAR(B2),MONTH(B2),1)

so the final formula would be something like:
=sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=(DATE(YEAR(B2),MONTH(B2),1)))*1,
(Sheet2!D))

HTH,
Keith

 

[ker_01]

If each value pair only occurs once ( no repeats), you might try using
sumproduct; something like (aircode):

[on sheet2!C2, where you want the value to show up for that row]
=sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=B2)*1, (Sheet2!D))

so if the first two conditions evaluate to true, they get a value of one,
which is multiplied against the 'month' value in column D to return just that
value.

However, it also appears that your original values are only the first of
each month, with no hours/minutes, and the comparison values can be any day,
along with hours/minutes. There are a few ways to do this, so I'll let you
pick your preference. For me I'd probably extract the year/month and create a
simplified date- something like:
=DATE(YEAR(B2),MONTH(B2),1)

so the final formula would be something like:
=sumproduct((Sheet1!A:A= A2)*1, (Sheet2!B:B=(DATE(YEAR(B2),MONTH(B2),1)))*1,
(Sheet2!D))

HTH,
Keith

 

[T. Valko]

Try this:

Dates table in the range Date!A421

Enter this formula in Actual!C4:

=SUMPRODUCT(--(Dates!A$4:A$21=A4),--(B4>=Dates!B$4:B$21),--(B4<=Dates!C$4:C$21),Dates!D$4$21)

Copy down as needed.

 

[T. Valko]

Try this:

Dates table in the range Date!A421

Enter this formula in Actual!C4:

=SUMPRODUCT(--(Dates!A$4:A$21=A4),--(B4>=Dates!B$4:B$21),--(B4<=Dates!C$4:C$21),Dates!D$4$21)

Copy down as needed.