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:
- Select the cell range specified for the table_array
argument or the Lookup_vector argument.
- On the Data menu, click Sort.
- 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)
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:
- Select the cell range specified for the table_array
argument or the lookup_vector argument.
- Click Cells on the Format menu, and then click the Number tab.
- In the Category box, click General. Then click OK.
- Edit your lookup formula or lookup table.