How To Use The HLOOKUP Function
In Excel + Examples

Written by co-founder Kasper Langmann, Microsoft Office Specialist.

When you’re working with a small spreadsheet, it’s easy to scan through and find the values you’re looking for.

When you start working with hundreds, thousands, or even tens of thousands of cells, though, that becomes impractical.

At this point, using Excel’s powerful lookup functions comes in handy. Excel has many great functions that let you search through your document to find specific pieces of data.

Kasper Langmann, Co-founder of Spreadsheeto

Today, we’ll be looking at HLOOKUP (or “horizontal lookup”), a great way to find data in a particular column.

Many people are familiar with the VLOOKUP (“vertical lookup”) function, but HLOOKUP is often unfairly overlooked.

In the rest of this article, you’ll see just how powerful HLOOKUP can be!

Introduction to HLOOKUP

The HLOOKUP function is rather simple.

You specify a column and a number, and it returns the value of the cell in that column, that many rows down. You could tell it to return the value of the 37th cell in column B, for instance.

That might not seem that impressive. After all, if you know the column and row of the data you need, why wouldn’t you just scroll there and find it?

HLOOKUP’s real power comes when you combine it with other functions. We’ll get to that shortly.

Kasper Langmann, Co-founder of Spreadsheeto

First, though, let’s look at the syntax of HLOOKUP:

Follow along with our free sample exercise file

Learning about HLOOKUP is much easier when you can try it yourself.

Download the free sample file below and follow along with our examples!

Download the FREE Exercise File

Download exercise file
Download free exercise file

HLOOKUP syntax

The syntax of HLOOKUP

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

Let’s break that down.

The lookup_value parameter is the column that you want to search. This is usually in the format of a column name, like “Salary,” but it could potentially search for any type of cell contents.

The table_array parameter contains a range of data, and lets Excel know where it should be searching. This will make more sense when we start using the function.

As you might expect, row_index_num is the number of the row that Excel will return data from.

range_lookup is an optional value. If it’s set to TRUE (or omitted), and you’ve sorted the data in the first row of your table_array from smallest to largest, it will return the largest value that’s smaller than the value of lookup_value. We’ll go through this in more detail shortly.

A practical example using HLOOKUP

Here’s an example of how you might use HLOOKUP.

In our example spreadsheet, we have three columns:

  • Full name
  • Department
  • Salary

We can use HLOOKUP to find a specific cell in any of those columns.

Let’s start with something really simple.

Here’s the best way to think about how HLOOKUP works.

You specify a column header name, and Excel finds that column. It then automatically returns a specified value from that column.

Keep that in mind while going through this example.

Let’s select the parameters of the HLOOKUP function.

First, we’ll add the lookup_value.

You’ll remember that lookup_value is the column you’re searching for.

So we’ll add “Full name” as this parameter. This tells Excel that we’re looking for a value in the “Full name” column.

Click into a blank cell, and start your formula with =HLOOKUP(“Full name”

Next comes table_array.

This is the area where Excel will search for the “Full name” column, so we’ll select the data we want to search (for both the column name and the specified cell).

In this case, it’s A1:A55. Excel will search the top row of this area. Because there’s only one column in table_array, Excel will only search the top row of column A for our column name.

We’ll continue our formula by adding this information: =HLOOKUP(“Full name”, A1:A55,

Finally, we’ll add 14 as the row_index_num parameter.

This tells Excel that we want the 14th value in the “Full name” column.

Our final formula looks like this:

This, as you might expect, simply returns the 14th value down in the Full name column. Not especially exciting. Let’s use range_lookup to spice it up a bit.

On the second sheet in the example file is a list of salespeople from the first sheet and their sales numbers for a range of specific dates.

Each of those dates corresponds to the number of sales an employee has made by that day.

Let’s try looking up a date that’s not listed in one of the columns.

Again, we’ll start with the lookup_value. For this task, we’ll add “05-16-17” as this parameter. This tells Excel that we’re looking for a column with “05-16-17” in the first cell.

Setting the table_array to B1:E13 ensures that Excel will look in the area that contains the sales numbers. This differs from our first formula in that it includes a number of cells in the first row. Excel will now search columns B through E for “05-16-17.”

And the row_index_num, which we’ll set to 4, will give us the fourth value in the column that we’re searching for.

In this case, the date used (05-16-17) isn’t listed, but we still get a return value. Because range_lookup defaults to TRUE (unless you change it), HLOOKUP uses the next largest value, which, in this case, is 05-15-17.

Again, this might not seem all that useful to you. If you know which row to look in, you can probably find the right column. But in a very large spreadsheet, HLOOKUP saves you the time of finding the right column; you can simply search for it.

Also, keep in mind that, for this to work, the column names need to be organized in ascending order.

Kasper Langmann, Co-founder of Spreadsheeto

This is rather rudimentary stuff. Let’s take a look at the real power of HLOOKUP by combining it with other functions. We’ll get to that in a moment.

First, we need to discuss wildcards.

Using wildcards with HLOOKUP

If you can’t remember exactly the text you’re looking for, you can use one of Excel’s best search features: wildcards. Including an asterisk (*) in a search term indicates that at least one letter is missing. “S*et” matches “sunset,” “sachet,” and “stet.”

A question mark (?) indicates that a single letter is missing. “S?et” matches “stet,” but not “sunset” or “sachet.”

Remember that these wildcards only work in text searches; if you’re looking for a specific number, but can’t remember what it is, you’ll need to use a different method.

Kasper Langmann, Co-founder of Spreadsheeto

These wildcards can be useful if you don’t remember the exact name of the column you’re looking for.

If you remember that the client you’re looking for starts with “Ph” and ends with “onic,” and maybe another letter, you can use “Ph*onic?”—this would match a client called “Photonica.”

Keep in mind that a wildcard will return the first match that it finds. So if there are multiple matches, you’ll only get one.

Excel won’t tell you that it would have found multiple matches, either. So include as much detail as you can when you’re using wildcards.

A note on losing formulas

If you tend to put formulas in random cells around your spreadsheet, it’s time to get a bit more organized.

Having formulas all over the place makes it hard to figure out what they’re telling you. And it can take a lot of time to figure it out if you’re coming back to a spreadsheet after a long time.

Here’s a quick way to stop losing your valuable work:

In the example below, we’ll be looking up an employee’s sales on a specific date by entering their name. Without a properly formatted method of storing your formulas, you’ll just see a number the next time you come back to your spreadsheet.

And that doesn’t tell you much.

So we’re going to make it easier by making it very clear what information we’re looking up.

First, let’s make sure we know what we’re putting into the formula and what we’re getting back out. In a cell near the top of the page, we’ll type “Name,” and in the cell below it, “Date.” Below that, “Sales.”

To make sure these cells stand out, we’ll make them bold and add a border.

Head to the “Home” tab in the Ribbon. Select these three cells and click the Bold option.

Then select these cells and the cell to the right of each, and select Thick Outside Borders in the Border drop-down.

Now, whenever we come back to this spreadsheet, we’ll know exactly what information is going into and coming out of our formula.

If you haven’t done this in the sample spreadsheet, do it now—it’s a good habit to get into.

Kasper Langmann, Co-founder of Spreadsheeto

Powering up HLOOKUP with MATCH

Excel’s MATCH function finds a cell with contents that match a search term, and returns the relative position of that cell. Here’s a quick example (back in the Employees sheet).

We’ll set MATCH’s lookup_value to “Kristi Hines,” the lookup_array to A1:A55 (which contains all of the names on the sheet), and the match_type to 0 (zero—this looks for an exact match).

MATCH returns “6” when I search for “Kristi Hines,” because Ms. Hines is in the 6th position in the column.

Let’s see if we can use that to make HLOOKUP a more useful.

Say you wanted to find out how many sales Steven Lamar had made on 5/15/17.

First, put “Steven Lamar” in the “Name” box, and “05-15-17” in the “Date” box so we don’t forget what we used in our formula. Now, when you’re entering your formula, you can click on the boxes that contain the search terms you’re looking for.

Here’s what the formula ends up looking like:

Make sure to include “0” (zero) as the final argument in the MATCH formula if you’re using text as a search term.

Kasper Langmann, Co-founder of Spreadsheeto

As you can see, the MATCH function that returns Steven Lamar’s position in column A has replaced row_index_num.

MATCH sets row_index_num to 5—and you don’t have to look up which row Mr. Lamar is in. It only saves you a few seconds in this particular case, but imagine if you had thousands of salespeople listed, and you wanted to find a single one. This could save you quite a bit of time.

To get the sales numbers for another salesperson, all you need to do is enter their name in the function.

Simplifying the process with data validation

By combining HLOOKUP with MATCH, we’ve made it significantly more powerful.

But it’s still a bit of a pain to enter the exact name of the person you’re looking for. What if you misspell the name? Or forget one of your salespeople?

Data validation can help.

There are many ways you can use Excel’s data validation to make the process easier, but we’ll stick with one: the list.

Kasper Langmann, Co-founder of Spreadsheeto

Click into a cell, then select the Data tab from the Ribbon. Click on Data Validation, and select Data Validation…

Change the “Allow” dropdown to List. Then select the list of names in column A for the Source.

You’ll now have a drop-down menu that contains all of the names from column A.

And when you select a new name from that list, the “Sales” box will automatically be updated with the results (as long as the date is present and the formula hasn’t been altered).

Isn’t that easier?

You could also create multiple dropdowns to let you get data more easily. A dropdown with the date, for example, would make it easier to get specific sales numbers.

Kasper Langmann, Co-founder of Spreadsheeto

Understanding HLOOKUP errors

There are three types of errors you might encounter with HLOOKUP:

  • #N/A: the lookup_value is smaller than the smallest value in the first row of table_array.
  • #VALUE!: row_index_num is less than one.
  • #REF!: row_index_num is greater than the number of rows in table_array.

If you see any of these errors, making a quick change to your formula (or confirming that you’ve selected the correct table_array) should fix it quickly.

Wrapping things up

As you can see, HLOOKUP is a powerful function when your data is arranged horizontally.

VLOOKUP is the go-to function when looking up data in a sheet. But, when the data changes from vertical to horizontal, HLOOKUP is your best friend.

Use HLOOKUP with wildcards, other functions, and data validation, to make it even more powerful!

2019-03-28T15:41:09+00:00