Pull Data From Two Tables

[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

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

Table 2 (Cases)

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

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.

 

[cbatrody]

Hi,

Instead of HLOOKUP you can try INDEX MATCH functions which are more robust.

Try the following in C2:
=INDEX($I$2:$M$5,MATCH($A2,$I$2:$I$5,0),MATCH($B2,$I$2:$M$2,0))

In D2: =INDEX($I$9:$M$12,MATCH($A2,$I$9:$I$12,0),MATCH($B2,$I$9:$M$9,0))


This is keeping in mind, you have the population table in I2:M5

Cases table in I9 to M12

the combined table is in A1:D2

PM your email address if you need the sample file that I created for you!

 

[FiddleRox]

Thank you. That worked perfectly.