I found this, which is pretty close, but I don't know VB, so I'm trying to do this with a vanilla formula.
First worksheet has a series of tables of unemployment rate: 1 per US state. Each table presents data with year as row label, month as column label, and unemployment rate in each cell. This is the standard format from the BLS website. I've added a column for each table corresponding to the state abbreviation.
Second table has a large number of incidents, each incident is a row. Columns are various data about the incident, including state abbreviation and date. I want to make a vlookup (or whatever is more appropriate/efficient) that pulls the corresponding state unemployment for the appropriate date for each incident (150k+ in total) from the first worksheet.
I'm guessing I would need to reorganize the tables in the first worksheet so that each month/year combination was a row, or use some combination of "match" and "if" and the like. A little lazy, but it has been years since I messed with much Excel so I figured I'd start here. I really appreciate anyone's input.
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 Apr 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 (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.