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.
- All versions of Excel
|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.|
Note: The HLOOKUP function is not case-sensitive. So, "Alakazam" and "ALAKAZAM" criteria are same.
- 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*"|
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.
[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.
If col_index_num is less than 1, you will get #VALUE! error.
If col_index_num is greater than the row number of the table_array, you will get #REF! error.