How do I find a match for three variables to determine the result?
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
"SykesvilleJim" wrote:
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!!
|