Microsoft KB Archive/181201

= HLOOKUP, VLOOKUP, LOOKUP return incorrect values in Excel =

Article ID: 181201

Article Last Modified on 1/22/2007

-

APPLIES TO


 * Microsoft Office Excel 2003
 * Microsoft Excel 2002 Standard Edition
 * Microsoft Excel 2000 Standard Edition

-



This article was previously published under Q181201



SYMPTOMS
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.



CAUSE
This behavior occurs when either of the following conditions is true:
 * The range specified for the "table_array" argument (LOOKUP) or the range specified for the "lookup_vector" argument (VLOOKUP and HLOOKUP) is not sorted in ascending order.
 * Number formatting is applied to the range that is hiding the underlying values.



RESOLUTION
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 order
To sort the table, follow these steps:
 * 1) Select the cell range specified for the table_array argument or the Lookup_vector argument.
 * 2) On the Data menu, click Sort.
 * 3) Make sure the left column in the selected range is selected in the Sort By list and make sure that Ascending is selected. Click OK.

Method 2: Using the Range_Lookup argument
If 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:

181212 Performing a Lookup with unsorted data in Excel

Method 3: Removing cell number formats
To remove specific number formats that may affect the displayed value, follow these steps:
 * 1) Select the cell range specified for the table_array argument or the lookup_vector argument.
 * 2) Click Cells on the Format menu, and then click the Number tab.
 * 3) In the Category box, click General. Then click OK.
 * 4) Edit your lookup formula or lookup table.



MORE INFORMATION
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:

VLOOKUP(lookup_value,table_array,col_index_num)

Additional query words: XL2000 XL2002 XL2003

Keywords: kbfunctions kbprb KB181201

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.