Article ID: 181201 - View products that this article applies to.
This article was previously published under Q181201
Although the Microsoft Excel HLOOKUP, VLOOKUP, and LOOKUP worksheet functions do not return an error value (for example "#N/A"), the returned value is incorrect. This behavior may occur even when the exact lookup value is found in the lookup table.
This behavior occurs when either of the following conditions is true:
To resolve this behavior, sort the lookup table in ascending order or use the range_lookup argument to specify that the lookup table is unsorted. Or, if you have applied special number formatting to the cells, remove it. To do this, the use following appropriate method.
Note The range_lookup argument is available only for HLOOKUP and VLOOKUP.
Method 1: Sorting the Lookup table in Ascending orderTo sort the table, follow these steps:
Method 2: Using the Range_Lookup argumentIf you are using HLOOKUP or VLOOKUP, enter FALSE for the range_lookup argument. This is the fourth and last argument.
For example, if you are looking for "apple" in a table that occupies cells $A$2:$C$50 and you want to return the value from the third column (column C) of the table, the function would be the following:
=VLOOKUP("apple",$A$2:$C$50,3,FALSE)Note The LOOKUP function does not support the range_lookup argument. If the lookup_vector cannot be sorted, use the INDEX and MATCH worksheet functions to replace the LOOKUP function. For more information, click the following article number to view the article in the Microsoft Knowledge Base:
(http://support.microsoft.com/kb/181212/ )Performing a Lookup with unsorted data in Excel
Method 3: Removing cell number formatsTo remove specific number formats that may affect the displayed value, follow these steps:
Lookup_vector is the second argument of the LOOKUP function, as in the following:
LOOKUP(lookup_value,lookup_vector,result_vector)Lookup_vector is the first row of the table_array (second) argument of the HLOOKUP function as in the following:
HLOOKUP(lookup_value,table_array,row_index_num)Lookup_vector is the first column of the table_array (second) argument of the VLOOKUP function as in the following:
Article ID: 181201 - Last Review: January 22, 2007 - Revision: 5.3