Getting #N/A from Vlookup when matching value exist in the lookup data range.

Hello,

I am trying to use vlookup function to match data from two systems. The data is related to work orders. I need to see which work orders are in what status on other system. So I dumped work order reports from both systems and was pulling status from 2nd worksheet on to first worksheet against work order number. IT works for most of the parts but on some rows it does not work. I tried making column A in both worksheets formatted to Number or Text etc still does not work. By fluke it worked on one row but not sure why. If I copy the formula to other row, still get N/A.

Sorting lookup data also did not help. Any hints from experts??

Thanks,
JK

I've had similar problems when importing data from non-Excel sources.

I don't think the problem is formatting. That just dresses up the look
of the item, it doesn't actually change the data. Try this...

In a blank cell, type the number 1. Copy it to the clip board. Now
highlight all of your other data and from the menu select Edit > Paste
Special > Multiply. This will have the effect of multiplying
everything by 1. The numbers won't change and though the alphanumerics
look like they don't change, something happens that allows the vlookups
to work.

Not sure why, but that usually does the trick.

- John

Hi John,

Bongo! it worked... Thanks for helping me out with this multiply trick. Worked perfectly. You made my day....

Best regards in the world.
JK