Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
TO DELETE ONLY NUMERIC VALUES IN A CHARACTER AND NUMERIC CELL IN | Excel Discussion (Misc queries) | |||
VLOOKUP with numeric and alphanumeric values | Excel Discussion (Misc queries) | |||
Find and Return Numeric Label based on (Numeric Value) Criterion | Excel Worksheet Functions | |||
Find Numeric Criterion in Column & Return the Numeric Value from Row above | Excel Worksheet Functions | |||
Numeric in Text to convert back to the form of Numeric for VLookup Purposes | Excel Discussion (Misc queries) |