HLOOKUP function is a lookup and reference function that can search a specific information in a selected table. In this guide, we’re going to show you how to use the HLOOKUP function and also go through some tips and error handling methods.
The HLOOKUP searches a value in the first row of a range and returns the value in specified row.
Supported versions
- All versions of Excel
HLOOKUP Syntax
Arguments
lookup_value | What you are looking for |
table_array | Where you are looking - the range where you want to look up the value |
col_index_num | What we want to know - the row number in the range containing the return value |
[range_lookup] | Optional. Whether you want a precise or approximate match - indicated as 0/FALSE or 1/TRUE. Default value is 1/TRUE. |
Examples
Example 1
Note: The HLOOKUP function is not case-sensitive. So, "Alakazam" and "ALAKAZAM" criteria are same.
Example 2
Tips
- Always use a sorted list when [range_lookup] argument is omitted, or set TRUE.
- Wildcards can be used for searching text values. Note that searching a wild card in a range of numeric values may return incorrect results.
Operator | Description | Criteria Sample | Criteria Meaning |
? | Takes the place of a single character | “Ala?azam” | 8-character word starts with “Ala” and finishes with "azam" |
* | Can take the place of any number of characters. | “Ala*” | Any number of character word starts with “Ala” |
~ | Use tilde in front of a question mark or an asterisk to actually find them | “Ala~*” | Equal to "Ala*" |
Common Issues
Wrong Value
If [range_lookup] is omitted or set to TRUE, check whether your first row is sorted. If you need to search an exact value that matches the values from the first row, set [range_lookup] to FALSE.
#N/A!
[range_lookup] = TRUE
If search value is smaller than the smallest value in the first row, you will get #N/A! error.
[range_lookup] = FALSE
If search value doesn't match any values in the first row, you will get #N/A! error.
#VALUE!
If col_index_num is less than 1, you will get #VALUE! error.
#REF!
If col_index_num is greater than the row number of the table_array, you will get #REF! error.