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

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



IntricateFool wrote:
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???

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" wrote:

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



IntricateFool wrote:
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???

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



IntricateFool wrote:
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" wrote:

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



IntricateFool wrote:
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???

Yuor help is much obliged.

The index(match) works... Thanks

"vezerid" wrote:

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



IntricateFool wrote:
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" wrote:

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



IntricateFool wrote:
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???

Thanks for the feedback.

Kostis


IntricateFool wrote:
Yuor help is much obliged.

The index(match) works... Thanks

"vezerid" wrote:

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



IntricateFool wrote:
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" wrote:

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



IntricateFool wrote:
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???