Knowledge Base

HLOOKUP, VLOOKUP, LOOKUP return incorrect values in Excel

Article ID: 181201

Article Last Modified on 1/22/2007

APPLIES TO

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


Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.