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
[TABLE="class: cms_table_grid, width: 500"]
<tbody>[TR]
[TD]State[/TD]
[TD]Year[/TD]
[TD]Population[/TD]
[TD]Cases[/TD]
[/TR]
[TR]
[TD]Drop List[/TD]
[TD]Drop List[/TD]
[TD]Result From Table 1[/TD]
[TD]Result From Table 2[/TD]
[/TR]
</tbody>[/TABLE]


Both tables would look similar to the following:

Table 1 (Population)

[TABLE="class: cms_table_grid, width: 700"]
<tbody>[TR]
[TD]STATE[/TD]
[TD]1960[/TD]
[TD]1961[/TD]
[TD]1962[/TD]
[TD]1963[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]AAAAA[/TD]
[TD]BBBBB[/TD]
[TD]CCCCC[/TD]
[TD]DDDDD[/TD]
[/TR]
[TR]
[TD]Alaska[/TD]
[TD]EEEEE[/TD]
[TD]FFFFF[/TD]
[TD]GGGGG[/TD]
[TD]HHHHH[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]IIIII[/TD]
[TD]JJJJJ[/TD]
[TD]KKKKK[/TD]
[TD]LLLLL[/TD]
[/TR]
</tbody>[/TABLE]



Table 2 (Cases)


[TABLE="class: cms_table_grid, width: 700"]
<tbody>[TR]
[TD]STATE[/TD]
[TD]1960[/TD]
[TD]1961[/TD]
[TD]1962[/TD]
[TD]1963[/TD]
[/TR]
[TR]
[TD]Alabama[/TD]
[TD]AAAAA[/TD]
[TD]BBBBB[/TD]
[TD]CCCCC[/TD]
[TD]DDDDD[/TD]
[/TR]
[TR]
[TD]Alaska[/TD]
[TD]EEEEE[/TD]
[TD]FFFFF[/TD]
[TD]GGGGG[/TD]
[TD]HHHHH[/TD]
[/TR]
[TR]
[TD]Arizona[/TD]
[TD]IIIII[/TD]
[TD]JJJJJ[/TD]
[TD]KKKKK[/TD]
[TD]LLLLL[/TD]
[/TR]
</tbody>[/TABLE]



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
State	Year	Population	Cases
Alaska
STATE
Alabama	AAAAA	BBBBB	CCCCC	DDDDD
Alaska	EEEEE	FFFFF	GGGGG	HHHHH
Arizona	IIIII	JJJJJ	KKKKK	LLLLL

<tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]1962[/TD]
[TD="align: right"][/TD]
[TD="bgcolor: #E2EFDA"]GGGGG[/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="bgcolor: #FFE699"]Cases[/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]
[TD="bgcolor: #FFE699, align: right"][/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]1960[/TD]
[TD="align: right"]1961[/TD]
[TD="align: right"]1962[/TD]
[TD="align: right"]1963[/TD]

[TD="align: center"]6[/TD]

[TD="align: center"]7[/TD]

[TD="align: center"]8[/TD]

</tbody>

Sheet4​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR="bgcolor: #DAE7F5"]
[TH="width: 10"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH="width: 10, bgcolor: #DAE7F5"]D2[/TH]
[TD="align: left"]=INDEX(A5:E8,MATCH(A2,A5:A8,0),MATCH(B2,A5:E5,0))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[FiddleRox]

Thank you. That worked perfectly.