help - problem with data using vlookup in Excel 2003

[JLH]

I'm using Excel 2003. I've got two worksheets of data in my workbook,
one copied from a text file, the other downloaded from the Bureau of
the Census, originally in .csv format and saved as an .xls file.

I'm using vlookup to use the state name in one sheet to lookup the data
value I want from the other worksheet. Vlookup returns the value of
the last cell in the table rather than the value that should be
returned.

I've done some simple testing on the lookup value relative to that in
the table array. Both entries are the same length. Both are text
(istext). Match returns false.

Any ideas as to what is causing my problem (the mismatch) and how to
fix it?

TIA,

John

 

[T. Valko]

One entry may have unseen characters like char 32 spaces and the other data
may have unseen characters like char 160 spaces.

A1 = Texas<char 32>
D10 = Texas<char 160>

Both have the same length but they still won't match.

Test the very first and last characters of each entry.

=CODE(LEFT(UPPER(A1))
=CODE(RIGHT(UPPER(A1)))

=CODE(LEFT(UPPER(D10)))
=CODE(RIGHT(UPPER(D10)))

If the state name was Texas then the results of those formulas should be:

=CODE(LEFT(UPPER(A1)) = 84
=CODE(RIGHT(UPPER(A1))) = 83

=CODE(LEFT(UPPER(D10))) = 84
=CODE(RIGHT(UPPER(D10))) = 83

If you get any results of 32 or 160 then you have those unseen whitespace
characters which are (fairly) common when importing/copying/pasting data
into a sheet.

There is a macro at this site that will remove all those whitespace
characters from your data. I use this all day long!

http://www.mvps.org/dmcritchie/excel/join.htm#trimall