Format into VLookup
 

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.
--
Chuck L

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----
"Chuck" wrote in message
...
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.
--
Chuck L

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.
--
Chuck L


"Max" wrote:

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
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik
----
"Chuck" wrote in message
...
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.
--
Chuck L

Glad to hear you got it to work, Chuck !
You're welcome ..
--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Chuck" wrote in message
...
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.
--
Chuck L