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'm trying to nest multiple lookups, which can't be the intelligent way to do this (formula In 2nd, incident-based worksheet).
Innermost/first lookup: use state in incident worksheet: find all applicable years in 1st worksheet that only apply to this state (I'm returning a range of rows, after searching by row?? state is a column, as is year. month is a row (first column header).
Middle/second lookup: use year in incident worksheet: find within returned range of years from above (a subset of rows), exact row, containing month Jan-Dec (remember that months are columns, ie column labels) for the appropriate State-Year pair.
Outermost lookup: use month in incident worksheet: find within returned range of months from second lookup, exact month (at this point, ideally this would be the exact unemployment rate).

Edited in: just trying to make it as clear as I can:

Worksheet 1) Unemployment Rates:
State Year Jan Feb Mar April etc
AK 1991 3.5 4.3 5 6.1 x
AK 1992 3.1 4.1 x x x
TX 1991 x x x x x
TX 1992 x x x x x
VA 1993 x x x x x
VA 1994 x x x x x

Worksheet 2) Incidents:
Exact Calendar Date Year State xxx xxx etc (formula to reference applicable unemp rate)
xx/xx/xxxx xxxx xx x x x ?????
xx/xx/xxxx xxxx xx x x x ?????


=hlookup(month(b2),vlookup(c2,(vlookup(av2,[in first worksheet with unemp. rates, all states, in alphabetical order and first column]A2:a1174,[years in worksheet 1 that apply to this state, range is all years, but nested lookup means it will only look at the ones with the correct state abbreviation, right?]b2:b1174),[in the unemp rate worksheet, for the 1 row year/state combination that is returned, search among all months]$c$1:$n$1),*****)

Trying to provide notes here to make the above more clear:
b2 = month in incident worksheet (#2 in my original post)
c2 = year in incident worksheet
av2=state abbreviation in incident worksheet
***** So this is one question among many. Conceptually, at this point I wanted to have the last hlookup function search by month among the appropriate year/state row, and return the exact cell I want. But, it has become so convoluted I don't know how to reference the last returned value.

Thank you for any help! Let me know if I can provide more clarity.

 

[Skybluekid]

You could use INDEX/MATCH to pull the data through

 

[Excel255]

That's what I thought - started to watch the ExcelisFun/Mr.Excel video on that on YT. Could you provide some brief initial guidance as to how I would do so?

 

[Skybluekid]

Assuming that on Worksheet 1 you data starts in A1

On Worksheet 2, I take it that after the state is the Month? And again your sheet starts in A1,If so, then you could try:

=INDEX(C2:F7,MATCH(C2&D2,Worksheet1!$B$1:$B$10&Worksheet1!$C$1:$C$10,0),MATCH(D1,Worksheet1!$A$1:$A$20,0)), Use Cntrl Shift and Enter

 

[Skybluekid]

Slight amendment

=INDEX(Worksheet1!C2:F7,MATCH(C2&D2,Worksheet1!$B$1:$B$10&Worksheet1!$C$1:$C$10,0),MATCH(D1,Worksheet1!$A$1:$A$20,0)), Use Cntrl Shift and Enter

 

[Excel255]

Assuming that on Worksheet 1 you data starts in A1

On Worksheet 2, I take it that after the state is the Month? And again your sheet starts in A1,If so, then you could try:

=INDEX(C2:F7,MATCH(C2&D2,Worksheet1!$B$1:$B$10&Worksheet1!$C$1:$C$10,0),MATCH(D1,Worksheet1!$A$1:$A$20,0)), Use Cntrl Shift and Enter

When you say "starts," do you mean the first data point is at A1? Worksheet 1 is awkward, first unemployment rate (for AK, 1991, January), is C2. Data is c2:n1169. "A" Column is State, followed by state abbreviations. "B" column is year. Month Labels are row 1, column c1:n1.

For Worksheet 2: first row is column labels, so data is a2. Format is:
Full-Calendar-Date/Year/Many Other Fields/State Abbreviation/(would love to put the unemp rates as new, last column, here)

 

[Excel255]

BTW ty for responding, more quickly and substantively than anyone on any stack overflow forum.

 

[Skybluekid]

In that case:

=INDEX(Worksheet1!$C$2:$N$1169,MATCH(B2&D2,Worksheet1!$B$1:$B$1000&Worksheet1!$A$1:$A$1000,0),MATCH(D1,Worksheet1!$C$1:$N$1,0)), Use Cntrl Shift and Enter

You might need to adjust the ranges

 

[Excel255]

I see, so, if this is more or less correct, the usage of Index+Match is more or less:
Index(what I want returned,Match(criteria to match),Match(criteria to match)). I need to go to sleep but I will try this out later today.

Thank you.

In that case:

=INDEX(Worksheet1!$C$2:$N$1169,MATCH(B2&D2,Worksheet1!$B$1:$B$1000&Worksheet1!$A$1:$A$1000,0),MATCH(D1,Worksheet1!$C$1:$N$1,0)), Use Cntrl Shift and Enter

You might need to adjust the ranges