I come across this every day. I usually just qualify my Vlookup. To do this
you enter

=IF(ISERROR(VLOOKUP(A1,C:D,2,FALSE)),VLOOKUP(A1*1, C:D,2,FALSE),VLOOKUP(A1,C:D,2,FALSE))

and A1 is the lookup value.
Where C:D is you price list.

There are more complicated ways to do it as well.


"Tias" wrote:

I often get this problem. A long list contains partnumbers (some numerical,
some text) and I need to get price from a pricelist. Even though it is an
exact match I get #N/A in return. Only way to get it working is to edit the
source cell (F2+Enter).
I have tried TRIM() and VALUE() but since the list contains both types I get
#VALUE in return for all text cells.

Is there a way to "transform" source column so that it will work 100%?