I need to lookup state based data in different tables on the same worksheet.
Each state has its own summary table, and I need to lookup values on another
worksheet based on these summaries. All the data is aligned accordingly so
that each table contains the same outputs in a vertcal style. So what I was
hoping was that I can perform something like a hlookup to search for the
state and return a value, but this does not seem to work out for me.

For example: based on all 50 states

In worksheet A - I have all 50 states listed in one column:
State|Value
AL |
CA |
HI |

I am trying =hlookup(CA,StateData,2,False) <- want it to return the second 9
from the column in worksheet B after looking through all states

In worksheet B - I have 50 seperate tables and the one column that I am
interested in looks like (only 2 states given in the example):

CA
9
9
2
8
1
0


HI
13
13
2
10
2
0

How would I go about looking up values based on the state? Like I said, I
thought I could just do a hlookup where it would search through to find a
particular state, and return the corresponding row, but does not work???

Any suggestions? Index, match???