Excel HLOOKUP: A Step-by-Step Tutorial on How to Look Up Data

With more than 400 built-in, easy-to-use functions at your fingertips, it’s no surprise that Microsoft Excel has become the most popular spreadsheet software in the world. If you’ve taken a look at our overview of the most essential functions for professionals, you may have noticed they all make it easier to spot trends, highlight patterns, and make data analysis easier than ever before — statements like INDEX MATCH, VLOOKUP, and SUMPRODUCT are just a few examples. You can now even illustrate those data insights using premade pivot tables, charts, and bar graphs. 

In this article, though, we’re going to focus on HLOOKUP and how you can use it to make performing data analysis as simple as ever. It’s useful because you can quickly find the data you need within a table based on preselected values. 

Before we get started, note that this tutorial is designed for beginners who have a basic understanding of Excel functions. If you’re not yet familiar with Excel functions, you may find it helpful to complete one of the introductory Excel courses on Udemy to get more comfortable with creating workbooks and managing spreadsheets.

If you’re confident with navigating the software, let’s get started!

How to use Excel’s HLOOKUP function

The goal of the HLOOKUP function is to search for items in a table and return the value in a corresponding row index. The term HLOOKUP is actually an abbreviation for horizontal lookup, so if you’re looking to search for items in a vertical table, opt for VLOOKUP instead. 

Beyond understanding its purpose, it’s good to know how the HLOOKUP formula works and what parameters the formula needs to look up the answer and perform the correct calculation. Fortunately, we will cover all of this in our tutorial. 

How to access the HLOOKUP function 

For this tutorial, imagine that you’re in charge of managing the inventory for a grocery store. In the spreadsheet below, you can find various produce items that our fictitious store has in stock, along with the barcode number and price of each item. 

If you’re familiar with the MATCH and LOOKUP functions, you may notice that the HLOOKUP function works very similarly to those. (If you’re not familiar with the MATCH or LOOKUP functions, you can master those in no time with The Excel LOOKUP Function: Power to Search your Spreadsheet and Excel Match Function: Ask and You Shall Receive, respectively.) All three functions can assist you in finding specific data within a spreadsheet. 

As for HLOOKUP, there are two ways that you can access the function within Excel.

1. Type the function directly into the target cell reference where you would like the answer to show up, along with all of the necessary parameters.
2. Use the Formulas menu to select the HLOOKUP function.

We will learn both of these methods so that you can decide which approach works best for you. 

Let’s use our example data to learn how we can apply the HLOOKUP function.

How to use HLOOKUP to find an item’s price

For this exercise, we will type an item name to have Excel retrieve the item’s price. We know that HLOOKUP is the best function to use in this case because we arranged our table horizontally.

To apply the HLOOKUP function, select the target cell you would like the answer to appear in and navigate to the Formula menu’s Lookup & Reference tab for the HLOOKUP function.

Once you’ve made it to the Lookup & Reference tab, choose HLOOKUP and add the parameters in the formula field. The image below will give you an idea of how the HLOOKUP formula will appear in Excel.

Accessing the formula through this method is convenient because it lets us insert the parameters we need without building the formula manually. Here’s how the parameters we set above will work together to return the data that we need.

• Lookup_value: The value you indicate here sets Excel up to return the information we’re looking for. Because we used the word ‘Apples’ in the example above, it will trigger Excel to look for every instance where Apple appears within the given table. 
• Table_array: With infinite rows and columns within the spreadsheet, table_array helps us set a designated table area where Excel can find the data we need. If we were to type Apple in a cell outside of the B5:I7 range, Excel would not include its price data in our results.
• Row_index_num: This instructs Excel to return a value in a particular row of the table. We set it to 3 in the example above so that it will return the price of the apple. Had we used 2, it would give us the barcode number for apples instead. 
• Range_lookup: When the range lookup is ‘FALSE,’ this tells Excel to find an exact match for the term we are searching for.

Here is the result that we would get using the parameters that we set:

Automating the lookup value

That approach is great if you only need to find information for one type of item, but there’s a better way to pull data from multiple rows within the table. Simply set the lookup_value to the cell where we want the item price to appear. From there, it will allow you to type the name of the item within the cell to bring up its sales price. 

Using C2 as our new lookup_value, here is what the updated formula looks like.

If we type Oranges in C2, Excel will now search for “Oranges” and return the price associated with that item.

Using a drop-down menu with HLOOKUP

When working with a large amount of data, entering a ton of different values can take up a lot of your time. Creating a dropdown list with all of the items is the perfect solution for this predicament.

Inserting a drop-down list within an Excel spreadsheet is very easy. Select cell C2 (where the dropdown box should appear), navigate to the Data menu, and select ‘Data Validation.’ Then, you’ll want to select ‘List’ from the ‘Allow’ menu and use the ‘Source” field to enter the range of cells you would like to include in the dropdown box. You can also create a drop-down list by referring to your worksheet’s named ranges. 

Now you can conveniently locate the price of each item just by selecting it on the drop-down list. 

Next Steps to learning Excel

HLOOKUP is not only one of the most important functions in Excel for analyzing and interpreting data, but also a great way to enhance your career prospects and master the way you approach data for personal or professional use. Now that you have a good understanding of how to use this essential formula, let’s take a look at Excel text functions.