Pull Data From Two Tables Using 2 Drop Lists

[FiddleRox]

I am attempting to match data (state and year) in two tables that have the same h row and v column headers by using 2 drop list

State	Year	Population	Cases
Drop List	Drop List	Result From Table 1	Result From Table 2

<tbody>
</tbody>

Both tables would look similar to the following:

Table 1 (Population)

STATE	1960	1961	1962	1963
Alabama	AAAAA	BBBBB	CCCCC	DDDDD
Alaska	EEEEE	FFFFF	GGGGG	HHHHH
Arizona	IIIII	JJJJJ	KKKKK	LLLLL

<tbody>
</tbody>

Table 2 (Cases)

STATE	1960	1961	1962	1963
Alabama	AAAAA	BBBBB	CCCCC	DDDDD
Alaska	EEEEE	FFFFF	GGGGG	HHHHH
Arizona	IIIII	JJJJJ	KKKKK	LLLLL

<tbody>
</tbody>

I need the 2 drop lists (State and Year) to pull data and match the desired cells (state and year) from each table into the corresponding cells (Population and cases). For example: Alaska and 1962 from both tables.

The code that I have in the cell "Result From Table 1" is - =VLOOKUP(B3,A12:CN62,HLOOKUP(C3,B10:CN11,2,FALSE),FALSE). That works the way it should.

The code that I have in cell "Result From Table 2" is - =VLOOKUP(B3,A79:CN129,HLOOKUP(C3,B77:CN77,2,FALSE),FALSE). This code give me the #REF! error.

How can I get my desired result in the cell "Result From Table 2"?

Please help. I thank you in advance.

 

[DRSteele]

Your HLOOKUP is reporting #REF because you've put a 2 in the 'row index' argument but have declared only one row in the 'table array' argument.

I think most would suggest INDEX & MATCH is a superior way to go. Like this:

Excel 2012

	A	B	C	D	E
1	State	Year	Population	Cases
2	Alaska	1962		GGGGG
3
4	Cases
5	STATE	1960	1961	1962	1963
6	Alabama	AAAAA	BBBBB	CCCCC	DDDDD
7	Alaska	EEEEE	FFFFF	GGGGG	HHHHH
8	Arizona	IIIII	JJJJJ	KKKKK	LLLLL

<tbody>
</tbody>

Sheet4​

Worksheet Formulas Cell Formula D2 =INDEX(A5:E8,MATCH(A2,A5:A8,0),MATCH(B2,A5:E5,0)) <tbody> </tbody>

<tbody>
</tbody>

 

[FiddleRox]

Thank you. That worked perfectly.