I don't use Vlookup much and it's not working for me. I know it's user error
so can you tell me what I did wrong?

I have two worksheets in my file. In the first one, I have a list of
addresses with a full unabbreviated US state name in column T, row 2. Column
U is blank because I want to put the two-digit state code in there (starting
with U2).

In the second worksheet I have the full state name in Column A and the
two-digit code for it in Column B (rows 1-50).

Here's my formula starting in U2:

=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE)

What is wrong?

Your formula is correct in syntax and structure.

Is it possible that there are extra spaces in the name that are causing
errors? For example, if T2 contains "New York " this would cause the formula
to fail (or unprintable characters, if info was copied from outside source
like the internet). If so, you might try:

=VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,F ALSE)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ocean mist" wrote:

I don't use Vlookup much and it's not working for me. I know it's user error
so can you tell me what I did wrong?

I have two worksheets in my file. In the first one, I have a list of
addresses with a full unabbreviated US state name in column T, row 2. Column
U is blank because I want to put the two-digit state code in there (starting
with U2).

In the second worksheet I have the full state name in Column A and the
two-digit code for it in Column B (rows 1-50).

Here's my formula starting in U2:

=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE)

What is wrong?

Drat! I tried your formula and it still doesn't work. New York is, of course,
one of the states. Could it be because not all cells in column T have
anything in them (yet)?

"Luke M" wrote:

Your formula is correct in syntax and structure.

Is it possible that there are extra spaces in the name that are causing
errors? For example, if T2 contains "New York " this would cause the formula
to fail (or unprintable characters, if info was copied from outside source
like the internet). If so, you might try:

=VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,F ALSE)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ocean mist" wrote:

I don't use Vlookup much and it's not working for me. I know it's user error
so can you tell me what I did wrong?

I have two worksheets in my file. In the first one, I have a list of
addresses with a full unabbreviated US state name in column T, row 2. Column
U is blank because I want to put the two-digit state code in there (starting
with U2).

In the second worksheet I have the full state name in Column A and the
two-digit code for it in Column B (rows 1-50).

Here's my formula starting in U2:

=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE)

What is wrong?

I found the problem. The look-up table was NOT clean. It had extra spaces
after the state names, probably because it was copied from the Internet as
you said. I took them out and it is working now.

Many thanks for your help.

"ocean mist" wrote:

Drat! I tried your formula and it still doesn't work. New York is, of course,
one of the states. Could it be because not all cells in column T have
anything in them (yet)?

"Luke M" wrote:

Your formula is correct in syntax and structure.

Is it possible that there are extra spaces in the name that are causing
errors? For example, if T2 contains "New York " this would cause the formula
to fail (or unprintable characters, if info was copied from outside source
like the internet). If so, you might try:

=VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2,F ALSE)

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"ocean mist" wrote:

I don't use Vlookup much and it's not working for me. I know it's user error
so can you tell me what I did wrong?

I have two worksheets in my file. In the first one, I have a list of
addresses with a full unabbreviated US state name in column T, row 2. Column
U is blank because I want to put the two-digit state code in there (starting
with U2).

In the second worksheet I have the full state name in Column A and the
two-digit code for it in Column B (rows 1-50).

Here's my formula starting in U2:

=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE)

What is wrong?

Sounds like should work as long as the full state names in T2:T51 match
those in StateCodes A1:A50

Why do you say "it's not working"?

Did you drag U2 down to U51?

What results do you get?

Is it possible you have extra space(s) in a state name in either sheet?


Gord Dibben MS Excel MVP


On Fri, 8 Jan 2010 13:16:01 -0800, ocean mist
wrote:

I don't use Vlookup much and it's not working for me. I know it's user error
so can you tell me what I did wrong?

I have two worksheets in my file. In the first one, I have a list of
addresses with a full unabbreviated US state name in column T, row 2. Column
U is blank because I want to put the two-digit state code in there (starting
with U2).

In the second worksheet I have the full state name in Column A and the
two-digit code for it in Column B (rows 1-50).

Here's my formula starting in U2:

=VLOOKUP(T2,StateCodes!$A$1:$B$50,2,FALSE)

What is wrong?

Luke

TRIM and CLEAN won't clear the 0160 non-breaking space if that's what the
problem is.

EditReplace

What: Alt + 0160

With: nothing

Is the only way to clear the nbsp


Gord

On Fri, 8 Jan 2010 13:31:02 -0800, Luke M
wrote:

Your formula is correct in syntax and structure.

Is it possible that there are extra spaces in the name that are causing
errors? For example, if T2 contains "New York " this would cause the formula
to fail (or unprintable characters, if info was copied from outside source
like the internet). If so, you might try:

=VLOOKUP(TRIM(CLEAN(T2)),StateCodes!$A$1:$B$50,2, FALSE)