How to Use HLOOKUP Function in Excel?

In Microsoft Excel, HLOOKUP or Horizontal Lookup function is generally used to extract data from a table or an array based on searching for a specified value in the topmost row and the corresponding column.

The above screenshot is an overview of this article, representing an application of the HLOOKUP function in Excel. We’ll use this dataset to illustrate our methods.

Introduction to the HLOOKUP Function

• Function Objective:

HLOOKUP function searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row. 

• Syntax:

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

• Arguments Explanation:

How to Use HLOOKUP Function in Excel: 8 Suitable Approaches

Example 1 – Finding an Exact Match

The table below represents the number of orders of some foods on some consecutive dates. Column H shows the number of canceled items or orders on those dates. By using the HLOOKUP function, we’ll find out how many fried chickens were sold on 7 August 2021.

Steps:

• In cell C15, enter the following formula:

• Press Enter.

The function returns 132. So, a total of 132 pieces of fried chickens was sold on the specified date.

In the 4th argument of the HLOOKUP function, we defined the range_lookup as FALSE, meaning function will search for an exact match of the lookup item ‘Chicken’ in the table.

Example 2 – Approximate Match

We can use Approximate Match by setting the 4th argument of the HLOOKUP function as TRUE. If our search criterion coincides partially with the exact name we’re looking for, then the function will consider that a match, and return it in the output cell.

Let’s replace the name of the food item ‘Chicken’ with ‘Kitchen’ to see if our function works accordingly, as ‘Chicken’ and ‘Kitchen’ sound almost similar.

Steps:

• In cell C15, enter the following formula:

• Press Enter.

The resultant value of 132 is returned.

So, by using TRUE in the 4th argument, the proper match was returned even after we typed the name of an item that is absent from the table. In cell C14, input some other different or partially matched names present in the table and see what the function returns.

Example 3 – Using HLOOKUP with MATCH Function

In order not to have to define the row number every time inside the HLOOKUP function, but rather change the date or value in cell C13 and find the result, we can use the MATCH function to define the row number of the data present in the corresponding cell.

For example, cells C13 and C14 show the date and the food item respectively. Let’s find out how many sliders were sold on that specified date in cell C13.

Steps:

• In cell C15, enter the following formula:

• Press Enter to return the result.

Now, every time we modify the date and name of the food item in cells C13 and C14, the output will update accordingly in cell C15.

Example 4 – Defining Named Range for Table Array inside the HLOOKUP Function

By defining the name of the table, we can replace the range of cells or array with the named range in the 2nd argument of the HLOOKUP function.

Steps:

• Select the entire table and go to the Name Box on the left top.
• Edit the name, for example to Food_orders.

• Select the output cell C15 where we’ll extract the number of sliders sold on 8 August 2021 and enter:

• Press Enter to return the result.

Example 5 – Inserting Wildcard for Approximate Match

By using the wildcard character Asterisk(*) before and/or after a text, we can look for the string containing that text inside. It’s an alternative to the Approximate Match discussed above but much more precise.

Suppose we want to find out the number of orders for a food item containing the text ‘Lime’ on 7 August, 2021.

Steps:

• In cell C15, enter the following formula:

• Press Enter to return the result.

Example 6 – Cell Reference from Another Worksheet

We can use the HLOOKUP function to extract data from other Excel worksheets too. For example, suppose we have a table like the picture below in Sheet1.

In Sheet 2, we’ll extract the data from Sheet1. Let’s find out the number of orders of the food item Sliders on 6 August 2021.

Steps:

• In cell C6 in Sheet2 enter the following formula:

• Press Enter.

The function returns the value 32 in Sheet1.

Example 7 – Extracting Multiple Values

By inserting an array containing the row numbers of the table inside the HLOOKUP function, we can pull out multiple data based on specific criteria.

For example, let’s find the number of the food item Limeade sold on 5 successive days beginning on 3 August, 2021.

Steps:

• In cell C17, enter the following formula:

• Press Enter,

The resultant values will be returned as an array in a row.

Example 8 – Using IFERROR with HLOOKUP to Modify Error Message If Found

When the HLOOKUP function can’t find a defined lookup value in the table, it’ll return a Value Not Available (#N/A) error. We can modify this error message to “Not Found” by using the IFERROR function.

IFERROR function returns value_if_error if the expression is an error and the value of the expression itself otherwise. The generic formula of this function is:

=IFERROR(value, value_if_error)

In our dataset, let’s now find out the number of sandwiches sold on 7 August 2021. If the item is absent in the table, the function will return a “Not Found” message.

Steps:

• In cell C15, enter the following formula:

• Press Enter to return the result based on the lookup criteria and defined message.

Things to Keep in Mind

HLOOKUP function only looks for the data based on the selected row(s). To extract data from selected column(s), use the VLOOKUP function.

HLOOKUP function searches for the value in the first row. If your data is present in another row, rearrange the table to keep the criteria in the first row.

HLOOKUP function is not case-sensitive, so it will consider ALEX and alex identical.

The 4th argument (range_lookup) of the HLOOKUP function is optional. Unless otherwise specified, the default value is TRUE, which represents Approximate Match.

Download Practice Workbook

<< Go Back to Excel Functions | Learn Excel