Convoluted Date Lookup

Question

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.

Edit:

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.

Answer 1

Since all the rates are numbers, you could use SUMIFS to get the lookups with multiple conditions. The only thing that would be left would be to get the correct month, and you can get that using INDEX and MATCH:

=SUMIFS(INDEX('Unemp. Rates'!C:N, 0, MATCH(TEXT(B2,"mmm"),'Unemp. Rates'!$C$1:$N$1,0)), 'Unemp. Rates'!A:A, AV2, 'Unemp. Rates'!B:B, B2)

Evaluating the innermost to outermost:

• TEXT(B2,"mmm"): Gives the month in text of the date in B2. Let's say the month is January. Jan will be the result here.

• MATCH("Jan",'Unemp. Rates'!$C$1:$N$1,0): Gives the number where "Jan" month is found. If it was Jan, you get 1 since it's the first cell.

• INDEX('Unemp. Rates'!C:N, 0, 1): Returns the 1st column from C:N that is C:C. 0 means all the rows, and 1 is what was previously obtained. If it were Feb, there would have been 2 and the column would have been D:D.

• SUMIFS(C:C, 'Unemp. Rates'!A:A, AV2, 'Unemp. Rates'!B:B, B2): returns the sum of values from the C column, provided the values in column A match the AV2 value (state), the values in column B match the B2 value (year).

I don't expect you to have more than one row with the same state and same year, so there's actually no 'SUM' taking place.