How to Use the HLOOKUP Function in Google Sheets

The HLOOKUP function performs a horizontal lookup and retrieves a value from a specific row in a table. Here's how to use it in Google Sheets.

When working with large sets of data, you often have to look up a specific value and find the corresponding value in the spreadsheet. Doing this manually can be slow, tedious, and time-consuming, so you can use the handy HLOOKUP function in Sheets instead. HLOOKUP stands for horizontal lookup and is a variant of VLOOKUP.

This article discusses the function, the differences between HLOOKUP and VLOOKUP, the syntax, and how you can use this function in your spreadsheets.

What Is HLOOKUP in Google Sheets?

Google Sheets' HLOOKUP function looks for a certain value in the first row of the table. It then retrieves a value from the column based on the index position specified by the formula parameter. The lookup parameters must be in the first row of the table.

The HLOOKUP function supports both approximate and exact matching. It is used for a horizontal table where the comparing data is in the top row, and you want to glance down a particular number of rows. If you’re already familiar with the VLOOKUP formula from Excel or Sheets, you can imagine that HLOOKUP works exactly the same but for tables that are set out on a horizontal plane.

This function attempts to seek a value in a dataset, and the value returned is the found value from a table provided.

Differences Between HLOOKUP and VLOOKUP

VLOOKUP is used to find the data in a vertically set out table, whereas HLOOKUP is used to find data in a horizontal table. VLOOKUP is more widely used than HLOOKUP since spreadsheets are typically vertical rather than horizontal.

The formulas for both functions are virtually the same, with the only difference being the row or column arguments. You may also need to familiarize yourself with the XLOOKUP function in Excel.

Syntax for HLOOKUP

The HLOOKUP formula uses four arguments, three of which are required for the formula to work, while one is optional. Before we discuss the arguments and what each one means, let's look at the syntax first. The syntax is:

=HLOOKUP(key, range, index, is-sorted)

Here is what each of the arguments used in the HLOOKUP formula do:

• key: this defines the function's value to perform the search.
• range: this argument defines the cell ranges we are performing the search on. Sheets will look for the key argument inside the first row in the defined range.
• index: this argument is used to define the index of the row which contains the value to be returned. Here, the first row starts from 1. If the value for this argument isn't between 1 and the total rows defined in the range argument, the #VALUE! error will be displayed.
• is-sorted: this is an optional argument used to define whether the row defined is sorted or not. By default, this value is true if left empty.

Examples of the HLOOKUP Function in Google Sheets

1. Performing a Simple HLOOKUP

This is the most basic way to use the HLOOKUP function. In this case, we want to find the sales made by a specific employee. Here are the steps you need to follow to do this:

1. Click on the cell where you wish to execute the formula.
2. Type in the initial part of the formula, which is =HLOOKUP(
3. Write the first argument here. In this case, it is the cell containing employee no.4, E1.
4. Add a comma.
5. Type in the second argument, which defines the cell range we are performing the search. In this case, it is the cell range A1:F3.
6. Add another comma to separate the arguments from one another.
7. Finally, type in the argument defining the row to return the value from. In this case, it is row number 2.
8. Add in a closing bracket to finish the formula.
9. Press Enter to execute the formula.

Now, in cell D9, the formula shows the number of sales that employee number 4 has made.

2. Creating a Nested HLOOKUP

This example requires us to find the employee who made the highest number of sales. To do that, we can use the MAX function nested inside the HLOOKUP function.

The MAX function allows us to find this highest number in the sales category. HLOOKUP will allow us to find the name associated with the number found using the MAX function. Although most steps are similar to what we did in the first example, there are some changes we need to make to use the MAX function. Here are the steps you need to follow:

1. Click on the cell where you wish to input the formula.
2. Enter the initial part of the formula, which is =HLOOKUP(
3. In the first argument, we are going to use the MAX formula. To do this, enter the initial part of the formula, which is MAX(. Note that we don't need to add an equal to the symbol here.
4. Type in the cell range to be checked to find the maximum value. In this case, it is B2:F2.
5. Add a closing bracket to close the MAX formula.
6. Add a comma to separate the two arguments.
7. Now, type in the cell range for the formula. In this case, these are the cells from A1:F3.
8. Add another comma to separate the arguments.
9. In the final argument, add the row number which you wish to get the value from. In this case, it is 3.
10. Add the final closing bracket to close off the formula.
11. Press Enter to execute the formula.

Drawbacks of HLOOKUP

This function has two shortcomings:

1. It always utilizes the first row in the input range to look up the search key. As a result, the HLOOKUP function cannot get a cell value that is above the lookup row.
2. This function is not dynamically sufficient, as entering a row within the input range does not update the row index automatically.

There is a solution to the previous two issues. You can use a combination of the INDEX and MATCH functions in Excel, similar to how the linked guide does.

Wrapping Up the HLOOKUP Function

Although the examples we used above use small sets of data, it’s easy to see how the HLOOKUP function would make it much easier to search full spreadsheets that are set up as horizontal tables.

This function is just one of the many ways you can navigate Google Sheets quicker, if you keep learning you’ll cut down the time you have to spend on data entry and retrieval.