Format into VLookup

[Guest]

I imported some data in a text format from Business Objects into Excel with a
VLookup table that I've used for a long time. When I copied the new data into
the cells, where the VLookup previously worked flawlessly, I now get the NA
error message. I checked the format of the cell that I just copied the data.
There are no changes that I can detect by using the "format function" of the
cells. I've tried copying the format from the cells above it where the
VLoopup works to no avail. I am convinced there is something in the format of
the cells I copied from the text file that is causing this problem but have
no idea how to resolve the problem. I appreciate any assistance. I have
50,000 rows to update.

 

[Max]

Just some thoughts ..

Formatting doesn't change the underlying cell values, so just applying the
same format won't work

If the imported lookup values are text numbers,
try Data > Text to Columns
to convert the whole lot to real numbers

Select the lookup column, say, col A
Click Data > Text to Columns
Click Finish

If the imported lookup values are text,
try wrapping a TRIM() around the lookup value in the formula,
e.g. use: =VLOOKUP(TRIM(A1), .... )
instead of: =VLOOKUP(A1, .... )

If the above didn't work, perhaps you could paste some sample data of the
lookup values in plain text in response here (Do not post any attachments),
and your VLOOKUP

 

[Guest]

Max:

You are a life saver! I followed your instructions converting the column to
text using your suggestion and it worked perfectly. I can't thank you enough.

 

[Max]

Glad to hear you got it to work, Chuck !
You're welcome ..