Different lookup

[Guest]

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???

 

[vezerid]

You say:

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

If I have understood your data layout correctly, simply enclosing "CA"
in brackets might do the job. Are you getting #NAME?

HTH
Kostis Vezerides

 

[Guest]

I am not actually using CA in the hlookup. I was just using that formular as
an example.... it is actually like C52 or something but i thought that would
just confuse everyone....

Also forgot to mentione that the data is layed out vertically in one column
due to the many tables that display data. Meaning, I can not reallign the
data to make it easier in a lookup. I was hoping there is a way to lookup the
state data from the example I have included in my original posting...

 

[vezerid]

So, if all data are in one column separated by state code, state code
is in C52 and The column containing your data is StateData you can use
the following:

=INDEX(StateData,MATCH(C52,StateData,0)+2)

HTH
Kostis Vezerides

 

[Guest]

Yuor help is much obliged.

The index(match) works... Thanks

 

[vezerid]

Thanks for the feedback.

Kostis

Yuor help is much obliged.

The index(match) works... Thanks