Excel 2007: Vlookup Problem

O

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

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

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

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.
 
G

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
 
G

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top