HLOOKUP Function

The Excel HLOOKUP function is a part of the Lookup and Reference function, contrary to the VLOOKUP function. Its abbreviation HLOOKUP stands for "Horizontal Lookup". It enables users to search for the value in the first row of the array and extract the data in the same column from a row you mentioned. For example, product pricing can be calculated using the HLOOKUP function depending on the price and quantity of the ordered bills.

Compatibility

This advanced Excel function is compatible with the following versions of MS Excel –

Excel for Microsoft 365
Excel for Microsoft 365 for Mac
Excel for the web
Excel 2024
Excel 2024 for Mac
Excel 2021
Excel 2021 for Mac
Excel 2019
Excel 2016
Excel 2013
Excel 2010
Excel 2007

Syntax

The syntax of the HLOOKUP function is as follows −

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments

You can use the following arguments with the HLOOKUP function −

Argument	Description	Required/ Optional
lookup_value	It specifies a numeric value, a cell reference, or a text. The lookup_up value must be found in the table's first row.	Required
table_array	It specifies the range of the table from where the value will be searched. A table of information in which data is looked up. Use a reference to a range or a range name. The field value in the table_array's first row may contain a numeric, Boolean value, or text string.	Required
row_index_num	It indicates the row number from where the resulting value will be extracted. For instance, if its value equals to 1, then it retrieves the the first row in the table_array. In the table array, the number of rows >= row_index_num >= 1	Required
range_lookup	It specifies a logical value: TRUE(for Approximate Match) or FALSE(for Exact Match). If range_lookup is skipped, an approximate match is retrieved. Additionally, if an exact match is not found, the next greatest value, smaller than lookup_value, will be extracted.	Optional

Points to Remember

table_array
- If the TRUE is set in the range_lookup, the values in the table_array's first row must be sorted in ascending order. Otherwise, HLOOKUP may not give the correct value and will not be sorted.
- There are no case differences.
- The values will be sorted in ascending order from the front to the rear side.
If the row_index_num specifies a zero or negative value, the HLOOKUP function will retrieve the #VALUE! error.
If a number is not specified in the row_index_num, then the HLOOKUP function will retrieve the #VALUE! error.
If the lookup_value is less than the smallest value presented in the table array's first row, then the HLOOKUP function will retrieve the #N/A error.
If the number of rows is smaller than the row_index_num in the table_array, the HLOOKUP function will retrieve the #REF! error.
If the range_lookup is set to TRUE and the lookup_value is not searched by the HLOOKUP function, it employs the second largest value.
If the range_lookup argument is set to FALSE and lookup_value is set to the text string, then in this case, wildcard characters like (?) or (*) can be utilized in the lookup_value.

Example of the HLOOKUP Function

Practice the following example to learn the use of the HLOOKUP function in Excel.

How to execute the HLOOKUP function in Excel?

In this example, we will use hit and trial to search for and extract the value in a few rows of the table array using the HLOOKUP function.

Solution

Step 1 − Consider the sample dataset, which comprises three columns: Jan, Feb, and Mar. The numeric values are specified in the range B2:D6.

Step 2 − After that, double-click on the C10 cell and write the formula =HLOOKUP(C8,B1:D6,C9,FALSE). In this expression, the lookup value is C8, which points to the FEB column.

Therefore, the HLOOKUP function will return the 100 as a result.

Step 3 − Moreover, double-click on the F10 cell and write the formula

And then, press the Enter tab.

Therefore, the HLOOKUP function retrieves the #N/A as the value is not found in the 11^th row of the table_array

Download Practice Sheet

You can download and use the sample data sheet to practice the HLOOKUP function.

Save or share this article