Convoluted Date Matching between 2 Worksheets

Excel255

New Member
Joined
May 11, 2014
Messages
11
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.
 

Some videos you may like

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

Excel255

New Member
Joined
May 11, 2014
Messages
11
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.
 
Last edited:

Excel255

New Member
Joined
May 11, 2014
Messages
11
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

Well-known Member
Joined
Apr 17, 2012
Messages
829

ADVERTISEMENT

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

Well-known Member
Joined
Apr 17, 2012
Messages
829
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

New Member
Joined
May 11, 2014
Messages
11

ADVERTISEMENT

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

New Member
Joined
May 11, 2014
Messages
11
BTW ty for responding, more quickly and substantively than anyone on any stack overflow forum.
 

Skybluekid

Well-known Member
Joined
Apr 17, 2012
Messages
829
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

New Member
Joined
May 11, 2014
Messages
11
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,996
Messages
5,575,433
Members
412,664
Latest member
akilah03
Top