vlookup data difficulty

[lhp]

An error message of "#N/A" is returned as the product of my vlookup formula
which references data in a worksheet that has been exported from an
accounting application into MS Excel. However, when the same item that is
being looked up is typed in then the desired result appears.

The export data is originally created in a version of MS Excel different
from that which I am using but I then save it into an Excel 97_2003 Workbook.
I am using Excel 2007. The formatting of the original data and that typed
in are the same.

Any thoughts?


Thanx,
lhp

 

[Gord Dibben]

Could be extraneous spaces in your data.

Or could be that what looks like numbers are actually text.

If a number is text, simply re-formatting will not change it to a number.

Provide a sample of your data and what throws the errors.


Gord Dibben MS Excel MVP

 

[Pete_UK]

If you are dealing with text values, then you might have unwanted
spaces (or more-likely non-breaking spaces) at the end of the data you
have imported.

If you are looking up numbers then it might be that they are numbers
in the lookup table but text values in the sought-item cell, or vice
versa.

Check for extra characters by using =LEN(cell) in a helper cell - does
this give the number of characters tha you expect?

Check for text values using =ISTEXT(cell) (which will return TRUE if
the cell contains text).

Check for numbers using =ISNUMBER(cell), and so on ...

Hope this helps.

Pete