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

[jdeshpa]

Hello,

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

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

Thanks,
J

 

[John Michl]

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

 

[jdeshpa]

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.
J