Excel 2007: Vlookup Problem

  • Thread starter Thread starter ocean mist
  • Start date Start date
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?
 
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)
 
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)?
 
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.
 
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
 
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
 
Back
Top