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. In this article, you’ll get to learn how you can use this HLOOKUP function efficiently in Excel.

The above screenshot is an overview of the article, which represents an application of the HLOOKUP function in Excel. You’ll learn more about the dataset as well as the methods to use the HLOOKUP function properly in the following sections of this article.

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

1. Exact Match with HLOOKUP Function in Excel

Let’s get introduced to our dataset first. The chart or the table below represents the number of orders of specific 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 in that restaurant.

📌 Steps:

➤ Select the output Cell C15 and type:

➤ Press Enter and the function will return with 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 have defined the range_lookup as FALSE, which means the function will search for the exact match of the lookup item- ‘Chicken’ in the table.

2. Approximate Match with HLOOKUP Function in Excel

Sometimes, we may forget what we’re looking for in a large range of data. In that case, we can use Approximate Match by defining the 4th argument of the HLOOKUP function TRUE. In that case, if our search criterion coincides partially with the exact name we’re looking for, then the function will look for that exact data and then return to the output cell.

So, assuming that we’re now replacing 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 the output Cell C15, the related formula will be:

➤ Press Enter, and you’ll get the resultant value of 132.

So, by using TRUE in the 4th argument, we’ve just got the proper match even after we’ve typed the name of a wrong item that is absent from the table. In Cell C14, you can now also input some other food names or partially matched names present in the table and see what the function returns with.

3. Use of HLOOKUP with MATCH Function in Excel

If you don’t want to define the row number every time inside the HLOOKUP function, rather you want to change the date or a value in Cell C13 and find the instant result, then you have to use the MATCH function to define the row number of that data present in the corresponding cell.

For example, Cells C13 and C14 show the date and the food item, respectively. So, we want to find out how many sliders were sold on that specified date in Cell C13.

📌 Steps:

➤ Select Cell C15 and type:

➤ Press Enter, and you’ll get the resultant value at once.

Now, every time you modify the date and name of the food item in Cells C13 and C14, the output will update accordingly in Cell C15.

4. Defining Named Range for Table Array inside the HLOOKUP Function

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

📌 Step 1:

➤ Select the entire table and go to the Name Box on the left top.

➤ Edit the name and, for example, we can define the table with Food_orders or anything you prefer.

📌 Step 2:

➤ Now select the output Cell C15 where we’ll extract the number of sliders sold on 8 August 2021 and type there:

➤ Press Enter and the resultant value will be shown right away. So, this is how we can define a named range inside the HLOOKUP function for the table_array argument.

5. Inserting Wildcard Character inside HLOOKUP Function to Search for Approximate Match

By using a wildcard character- Asterisk(*) before and after a text, we can look for the exact word containing that text inside. It’s a sort of alternative to the Approximate Match that was discussed in the second method but the use of wildcard character- Asterisk will go for the exact search with more precision.

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

📌 Steps:

➤ In the output Cell C15, the related formula with the wildcard character will be:

➤ After pressing Enter, you’ll find the exact data extracted from the table.

6. Cell Reference from Another Worksheet with HLOOKUP Function

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

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

📌 Steps:

➤ Select Cell C6 in Sheet2 and type:

➤ Press Enter and the function will return the value 32 from the table present in Sheet1.

7. Extracting Multiple Values with HLOOKUP Function in Excel

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, we’re going to find the number of the food item – Limeade sold on successive 5 days beginning from the date of 3 August, 2021. 

📌 Steps:

➤ In the output Cell C17, the related formula will be:

➤ After pressing Enter, the resultant values will return as an array in a row.

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 by replacing it with “Not Found” by using the IFERROR function outside

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, we’ll 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:

➤ Select the output Cell C15 and type:

➤ Press Enter and you’ll get the resultant value at once 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) and if you need to extract data from the selected column(s), you have to use the VLOOKUP function.

🔺 HLOOKUP function searches for the value in the first row. So if your data is present in another row, make sure that you’re going to rearrange the table and keep the criteria in the first row.

🔺 HLOOKUP function is not case-sensitive. So it means the function will consider ALEX and alex identical.

🔺 As the 4th argument(range_lookup) of the HLOOKUP function is optional if you don’t mention the value of this argument, the function will take it as TRUE which represents the Approximate Match.

Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.

Conclusion

I hope all the methods mentioned above to use the HLOOKUP function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.