Excel 2007: Vlookup Problem

[ocean mist]

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 M]

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)

 

[ocean mist]

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

 

[ocean mist]

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.

 

[Gord Dibben]

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

 

[Gord Dibben]

Luke

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

Edit>Replace

What: Alt + 0160

With: nothing

Is the only way to clear the nbsp


Gord