HLOOKUP, VLOOKUP, LOOKUP return incorrect values in Excel
Article translations Article translations Article ID: 181201 -
View products that this article applies to. This article was
previously published under Q181201 Expand all | Collapse all  On
This Page   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:   • 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.   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 num