VLOOKUP cannot find numeric values
 

I have a VLookup function that searches an external file for values.
The format in column A of the file is 'general', and all the numbers
are 'text numbers'.

In my spreadsheet, the column, column A, used as the lookup value is
also formatted as 'general', and there may be text or numeric values in
that column. When I do my VLOOKUP:

Range("B8").Select
' col A is the lookup val, hence RC[-1]. New_desc is named range in ext
file

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-1],'C:\Docs\a\MyDocs\lookupList.xls'!New_desc,2,FALS E)"

it can find all the text values, but all numeric values come back as
#N/A. If I change my column A in my spreadsheet to Text, then I get
back #NAME for all values.

Any ideas why numbers cannot be found (there are matches in the ext
file)? Do they have to be in the same numbet format?

-Phil

VLOOKUP cannot find numeric values
 

If it is looking for a numeric value in a text column, it won't match. If
the id values in the lookup table are all text, you could convert the lookup
value, like so TEXT(1,"0")

--

HTH

RP
(remove nothere from the email address if mailing direct)


"philserve" wrote in message
oups.com...
I have a VLookup function that searches an external file for values.
The format in column A of the file is 'general', and all the numbers
are 'text numbers'.

In my spreadsheet, the column, column A, used as the lookup value is
also formatted as 'general', and there may be text or numeric values in
that column. When I do my VLOOKUP:

Range("B8").Select
' col A is the lookup val, hence RC[-1]. New_desc is named range in ext
file

ActiveCell.FormulaR1C1 =
"=VLOOKUP(RC[-1],'C:\Docs\a\MyDocs\lookupList.xls'!New_desc,2,FALS E)"

it can find all the text values, but all numeric values come back as
#N/A. If I change my column A in my spreadsheet to Text, then I get
back #NAME for all values.

Any ideas why numbers cannot be found (there are matches in the ext
file)? Do they have to be in the same numbet format?

-Phil