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.