TROUBLESHOOTING VLOOKUP - PASTE NEW VALUES

[Charlie Marcous]

I have a workbook where column "I" looks up the value
in "J" in another worksheet and returns a correct value
for column "I" which works for 400+ rows just fine. When I
delete the values in "J", then paste new ones I receive
via e-mail attachment, I get #N/A instead of the lookup
values. I have played with formating as text, general,
number, etc. But I cannot get this to work properly 100%.
It works for 10% - 80% of the pasted values. Sometimes
keying over the pasted value will cause vlookup to work.

Thanks in advance for any help here.

 

[L. Howard Kittle]

Hi Charlie,

The numbers may look the same but the lookup value may be a number stored as
text. Find an offending lookup value and use this formula to check if it
really matches the other sheet lookup value: =A1=I5.

Where A1 is the lookup value in the formula and I5 is in the table array.
If it says FALSE you will need to clean up your pasted in lookup values.

If the paste in info is always going to give you trouble perhaps adding this
to your formula will help. Note that the A1 lookup value is multiplied by 1
to return it to a number. If it is already a number, no harm no foul.

=VLOOKUP(A1*1,D1:E5,2,0)

HTH
Regards,
Howard