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

  • Thread starter Thread starter SykesvilleJim
  • Start date Start date
S

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!!
 
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: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:D))

HTH,
Keith
 
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: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:D))

HTH,
Keith
 
Try this:

Dates table in the range Date!A4:D21

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:D$21)

Copy down as needed.
 
Try this:

Dates table in the range Date!A4:D21

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:D$21)

Copy down as needed.
 
Back
Top