Function: HLOOKUP

by | Nov 7, 2018 | Excel Formulas

HLOOKUP Example 1

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

HLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

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

=HLOOKUP(O2,C2:L16,2,FALSE)
 formula searches for the value of cell O2 ("Alakazam") in the first row of the table C2:L16. When [range_lookup] argument is FALSE, the function assumes that the search value exists in the first row. If the formula finds the value, it returns the value from the 2nd row of matched column.

HLOOKUP Example 1

Note: The HLOOKUP function is not case-sensitive. So, "Alakazam" and "ALAKAZAM" criteria are same.

Example 2

=HLOOKUP(O5,B2:L16,3,TRUE)
formula searches value of cell O2 ("145") in first row of the table B2:L16. When [range_lookup] argument is TRUE, the function assumes that the first row is sorted either numerically or alphabetically. The formula returns the value from the 3rd row of the matched column, if it finds the closest value.

HLOOKUP Example 2

Download Workbook

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.