Vlookup datatype(?) issue
 

I've been using vlookup to combine reports extracted from our erp system. I
open them as text (.csv) files into Excel.

most of the time vlookup works ok, but every now and then I just can't get
the function to find anything, resulting #N/A. In these cases it starts
working if I go to edit the lookup_value cell and do nothing else than press
enter (i.e. pressing F2 and enter). But with 15k rows it is not fun...

I guess it has something to do with datatypes of the lookup_value and the
table_array? I have tried to modify the type, but it doesn't seem to have
effect.

I've seen it often though I've never had an opporunity to reproduce the
effect.
Somehow due to the datatype when importing.

When you're in the Text Import Wizard, take care to format the columns
property (General / Text / Date)

For correcting the problem cells
One trick is to copy a blank cell, highlight the lookup_value cells then
pastespecial-values with addition.


--
Rob van Gelder - http://www.vangelder.co.nz/excel


"ars" wrote in message
...
I've been using vlookup to combine reports extracted from our erp system.
I
open them as text (.csv) files into Excel.

most of the time vlookup works ok, but every now and then I just can't get
the function to find anything, resulting #N/A. In these cases it starts
working if I go to edit the lookup_value cell and do nothing else than
press
enter (i.e. pressing F2 and enter). But with 15k rows it is not fun...

I guess it has something to do with datatypes of the lookup_value and the
table_array? I have tried to modify the type, but it doesn't seem to have
effect.