Convoluted Date Matching between 2 Worksheets

[Excel255]

Hello,
I have two worksheets.

1) Row Names = Year
Coumn Names = Month
Each cell has an unemployment rate. There is also a column for state abbreviation. So it is unemployment rate, by month, by year, by state.

This data was originally comprised of separate tables for each state, again with year as the row name (first column), and months as the column header for each state's table. I've condensed it, but I'm not sure I've made it any easier.

2) Incidents, each is a row. Columns are various data about the incident, including date and state abbreviation.

So, I want to use the state and date from the 2nd worksheet to automatically fill pull in the appropriate the unemployment rate for that state/date from the first worksheet.

 

[Excel255]

So, I thought that this would work:

=SUMIFS(INDEX([cc.xlsx]Sheet1!C:N, 0, MATCH(TEXT(B2,"mmm"),[cc.xlsx]Sheet1!$C$1:$N$1,0)), [cc.xlsx]Sheet1!A:A, AU2, [cc.xlsx]Sheet1!B:B, C2)

The 3-letter return is the month. "cc" is the Unemployment Rate worksheet. Since there is only ever one year+state combination, we thought it would always sum a single number (return it). But it always gibes me "0", so I think it never finds a match for the conditions. Any thoughts?

 

[Aladin Akyurek]

So, I thought that this would work:

=SUMIFS(INDEX([cc.xlsx]Sheet1!C:N, 0, MATCH(TEXT(B2,"mmm"),[cc.xlsx]Sheet1!$C$1:$N$1,0)), [cc.xlsx]Sheet1!A:A, AU2, [cc.xlsx]Sheet1!B:B, C2)

The 3-letter return is the month. "cc" is the Unemployment Rate worksheet. Since there is only ever one year+state combination, we thought it would always sum a single number (return it). But it always gibes me "0", so I think it never finds a match for the conditions. Any thoughts?

The formula looks syntactically correct. By the way, SumIfs will not work with a closed book.

Care to post the values that you have in:

B2
AU2
C2

 

[Excel255]

Thanks for the response.

Good to know about Sumifs. I figured once I had it in, I would just paste values over everything. And the "cc" book referenced is indeed open.
"B2" is calendar date xx/xx/xxxx for that row (in Incident worksheet, where the formula is placed), to reference for the month. Month in the Unemployment worksheet ("cc") is 3-character abbreviation (Jan/Feb/etc).
"AU2" is the state abbreviation for that row in Incident worksheet. C2 is the year for that row in Incident worksheet.
In the Unemployment worksheet, State abbreviation is in column A, and year is in column B. Months are columns C through N. Does the data in the Unemployment worksheet have to be in a certain order by one of the fields like alpha for State Abbrev. or numerical by year?

 

[Excel255]

Made both available on OneDrive, should make it way easier to diagnose. In cell "AX" of "Incidents" you will seem my most recent formula attempt. "cc" is the unemployment rate database.

https://onedrive.live.com/redir?resid=8E72133687ABB879%21921

Thanks for the response.

Good to know about Sumifs. I figured once I had it in, I would just paste values over everything. And the "cc" book referenced is indeed open.
"B2" is calendar date xx/xx/xxxx for that row (in Incident worksheet, where the formula is placed), to reference for the month. Month in the Unemployment worksheet ("cc") is 3-character abbreviation (Jan/Feb/etc).
"AU2" is the state abbreviation for that row in Incident worksheet. C2 is the year for that row in Incident worksheet.
In the Unemployment worksheet, State abbreviation is in column A, and year is in column B. Months are columns C through N. Does the data in the Unemployment worksheet have to be in a certain order by one of the fields like alpha for State Abbrev. or numerical by year?

 

[Excel255]

I got it .

Year in Incident was a "Date" format, but a number in "cc". just do "Year(c2)" at the end. Solved! Thanks anyways, I appreciate the interest and responses.

 

[Aladin Akyurek]

I got it .

Year in Incident was a "Date" format, but a number in "cc". just do "Year(c2)" at the end. Solved! Thanks anyways, I appreciate the interest and responses.

Great. If you insist on "summing" instead of retieveing...

=SUMPRODUCT(
  INDEX([cc.xlsx]Sheet1!C:N, 0, MATCH(TEXT(B2,"mmm"),[cc.xlsx]Sheet1!$C$1:$N$1,0)), 
  --([cc.xlsx]Sheet1!A:A=AU2),
  --([cc.xlsx]Sheet1!B:B=C2))

No risk retrieval formula would be one with Index/Match as already suggested above. What follows avoids concatenation (which can be another source of risk if not done properly)...

Control+shift+enter, not just enter:

=IFERROR(INDEX([cc.xlsx]Sheet1!C:N,
  MATCH(1,IF([cc.xlsx]Sheet1!A:A=AU2,IF([cc.xlsx]Sheet1!B:B=C2,1)),0),
  MATCH(TEXT(B2,"mmm"),[cc.xlsx]Sheet1!$C$1:$N$1,0)),"")

Note. It's better to avoid whole column references in the above formulas for reasons of efficiency.