VLookup Returning N/A - Formatting Problem

[Chelsea]

Hello:

I having a problem with a VLookup formula. My main spreadsheet is a
download from a mainframe and the lookup table (on a separate
spreadsheet) is inputted directly into Excel. The lookup value is a PO
number.

All the VLookup Formulas are returning N/A. The weird thing is that
if I manually type in the PO Number in the main spreadsheet, then the
formulas work. Or if I copy the PO Number from the main spreadsheet
into the lookup table, then the formulas work. Obviously the PO
numbers in the main spreadsheet and the PO numbers in the lookup table
aren't matching, but I can't find out why or what doesn't match.

I've tried the following solutions:

-reformatting the Vlookup Table and the main spreadsheet to General,
Number and Text
-Saved the main spreadsheet as as a CSV (supposedly to strip out any
odd formatting), and imported it back into the workbook. Still all
N/A.
-Formatted the number in the formula - VLookup(Text(A4,"General)
PurchaseOrders, 2)...which also didn't work.

My guess is that the numbers downloaded from the mainframe have some
odd formatting or something, and therefore they are not being matched
in the lookup table. But since I can't find what is different in the
two list of PO numbers, I can't fix it.

Any help would be appreciated.

Chelsea

 

[Frank Kabel]

Hi
try the following with your imported data:
- select an empty cell and copy this cell
- select your imported data
- goto 'Edit - Paste Special' and choose 'Add'

 

[Chelsea]

Hi Frank -

That worked! I have no idea why it worked, but it worked. Is there
an explanation?

Thanks!

Chelsea

 

[Frank Kabel]

Hi
your imported values were stored as 'Text' even if they look like
numbers. This procedure forces Excel to convert them back to real
numbers