How to Use HLOOKUP in Excel

Essentially, to use HLOOKUP in Excel you just have to enter the function name, the lookup value, table array, row index, and range. Need a more detailed explanation? This guide will show you how to use this function with several examples. Read on to learn more.

What Is HLOOKUP in Excel?

HLOOKUP is a convenient function in Excel that allows users to search for a defined value in a table’s top row and then get a corresponding value from the column of the specified row. The “H” in HLOOKUP stands for horizontal, meaning that the function searches horizontally across the rows in the table. The working of this function is very similar to the VLOOKUP function, which searches vertically across a column.

HLOOKUP Excel Syntax

Before we take a look at the HLOOKUP formula in Excel with examples, here is the syntax for the HLOOKUP formula:

=HLOOKUP(lookup_val, table_array, row_index, range)

The HLOOKUP formula in Excel requires three arguments. These are:

• lookup_val – This is a required value that defines the value the function should look for in the first row of the table. This parameter can be a numerical value, a text string, or a cell reference.
• table_array – This required value defines the table where the function will look for the data. This parameter can be a range name or a reference to a range. The values contained in the first row of this parameter can be numerical, strings, or logical
• row_index – This is a required parameter used to define the number of rows from where the matching value will be taken. This parameter defines the row number from the table_array parameter.
• range – This is an optional parameter where you can input a logical value to specify whether you wish to find an exact or an approximate match. An approximate match will be given if the value is TRUE or empty. The function will look for an exact match if the value is FALSE. The function will return an #N/A error if an exact match isn’t found.

Here are a few things to know about the HLOOKUP formula:

1. If the row_index parameter is 0 or less, then the HLOOKUP function in Excel will return the #VALUE! Error.
2. If the row_index parameter is higher than the total number of rows, then HLOOKUP in Excel will give the #REF! Error.
3. If the range parameter is TRUE, the values in the first row should be added in ascending order, or the function won’t output the correct value.
   1. Numbers should be written as -1, 0, 1, 2.
   2. Text should be written as A, B, C, D
   3. Logical expressions should be written as FALSE, TRUE

4. If the HLOOKUP function can find the lookup_val and the range is TRUE, it will use the largest value that is lesser than the lookup_val.
5. If the range is defined as FALSE and the lookup_val parameter is a text value, then you can use wildcard characters like an asterisk (*) and question mark (?) in the lookup_val parameter.
   1. Asterisk (*) can be used to match a sequence of characters.
   2. Question mark (?) can be used to match a single character.

If you use Google Sheets, you may want to check out our guide on how to use HLOOKUP in Sheets.

How to Use HLOOKUP in Excel

Now that we know the syntax of the HLOOKUP formula in Excel, let’s look at some examples of the formula in action.

Finding an Exact Match HLOOKUP Example

In this example, we will use the HLOOKUP function to find an exact match in the table. The table here has days in the columns and some sold items in the top row. We will find the number of “Notebook” items sold on Day 2. Here is how you can use HLOOKUP to find an exact match:

1. Open the workbook and click the cell where you wish to enter the formula.
2. Enter the starting segment of the HLOOKUP formula, which is =HLOOKUP(. After entering the initial part, you can see the formula’s require
3. d parameters.
4. We will type the value we wish to look up for the first parameter. In this example, we want to find out the sales of notebooks for a particular day. Type in “Notebook” for the first argument. Make sure to add string parameters in quotation marks.
5. Add one Comma ( , ) to separate the parameters.
6. For the second argument, we will write the table address where we wish to look up the value. In this case, the table is B1:G8. The first row of the table can have strings, numbers, or logical values, so you can include the header bar of the table in the second parameter. Add another comma to separate the parameters.
7. We will add the cell from where the matching value will be taken for the third parameter. In this example, we will write 3 as the value is in the third cell from the top in the table_array parameter. Add one comma to separate the parameters.
8. Finally, we are going to add the optional range parameter. In this example, we are looking for an exact match, so we can type 0 or FALSE here, and the HLOOKUP function will look for an exact match for the “Notebook” parameter.
9. Add a bracket to complete the formula and press Enter to execute it.

Finding an Approximate Match HLOOKUP Example

Let’s say you have words in the table that can have different spellings. For example, “jewelry” can be spelled as “jewellery”. In this case, you can use the range parameter to have the function look for an approximate match. For this example, we will misspell jewelry and use HLOOKUP to find an approximate match.

In this case, the process of using the HLOOKUP formula is the same as the one for the previous example. Here is how you can use HLOOKUP to find an approximate match:

1. Open the workbook where you wish to use the HLOOKUP formula and enter the initial part, =HLOOKUP(.
2. Add the string you want to look for as the first parameter. In this example, we are writing it as “Joolry”. Add one comma and write the table address, which is B16:G24.
3. Add another comma and write the value you want to get. In this example, we are going to write 2.
4. Now, for the last parameter, we will write the range parameter specifying whether we want to look for an approximate or an exact match. We are looking for an estimated match to write 1 or TRUE in this case.
5. Finally, add a bracket to complete the formula and press Enter to execute it.

A lot of people use both Sheets and Excel. Here is how you can convert Sheets to Excel and vice versa.

HLOOKUP From Another Worksheet

Using multiple worksheets allows you to organize your data into smaller sections. This becomes particularly useful when you wish to hide the data’s actual table. In this example, we are going to have two worksheets. The second worksheet will contain the data while we execute the formula in the first worksheet.

Here is how you can use HLOOKUP to find value from another worksheet:

1. Open the workbook where you want to use the HLOOKUP formula.

2. To go to the worksheet, click on the sheet’s name in the bottom footer bar. In our example workbook, we wish to use the formula in Sheet1.

3. Here, click n the cell where you wish to enter the formula and enter the initial segment of the HLOOKUP formula, which is =HLOOKUP(.
4. For the first parameter, we have a cell that contains the string value for the lookup value. It’s stored in cell F2. Add one comma to separate the parameters.
5. We will add the table’s address from the second worksheet for the second parameter. To do this, write the sheet’s name, add an exclamation mark ( ! ), and the address of the table. Add one comma to separate the two parameters. In this example, the parameter is Sheet2!A1:G9.
6. We will write the third parameter, defining the cell from where the value will be taken. In this example, we wish to get the value from 5.
7. Finally, we will add the optional range parameter. We want an exact match in this example, so we write 0 here.
8. Add a closing bracket to complete the formula and press Enter to execute it.

Google Sheets and Microsoft Excel seem very similar to each other in functionality. However, these tools can be very different from one another once you start to look at some of the more complex functionality. Here is an in-depth comparison of Sheets and Excel.

Reasons Why the HLOOKUP Function May Not Work

Excel formulas can be very specific, meaning that the function may fail to work if you don’t add the parameters in the proper format. Here are some of the reasons why HLOOKUP may not be working:

• The lookup_val parameter is not present in the first row.
• You didn’t use the proper reference when writing the formula.
• The lookup_val exceeds 255 characters.
• The formula has extra spaces in the parameters. Use the TRIM function to remove any extra spaces.
• If the lookup_val parameter is a string, then make sure it’s in quotation marks.

Microsoft recently added a new function called XLOOKUP to Excel. This function isn’t available in Google Sheets currently. Here are some alternatives to XLOOKUP in Sheets.

Frequently Asked Questions

What Does HLOOKUP Do?

The HLOOKUP formula is a function in Google Sheets and Microsoft Excel that allows the users to search for values in the top row of a table and then return a corresponding value from a row specified by the user in that table.

What Is the HLOOKUP Formula?

The syntax for the HLOOKUP formula is =HLOOKUP(lookup_val, table_array, row_index, range). The formula requires three parameters to work. The lookup_val parameter defines the value the function should look for in the first row of the table. The table_array parameter defines the address for the table where the function will look for the data. The row_index argument defines the row’s location from where the matching value will be taken.-

What Are the Differences Between Sheets and Excel?

There are multiple pros and cons of using Google Sheets and Excel. Google Sheets is free for individuals and small businesses, whereas Microsoft Excel costs money. However, Google Sheets does not have a lot of data visualization options, whereas Microsoft Excel features a better charts feature set. Microsoft Excel features advanced functions, whereas the formulas in Google Sheets are less customizable.

Can Sheets Do Everything that Excel Can?

Both Sheets and Excel work similarly. However, Sheets is better suited for someone who isn’t looking to do a lot of complex calculations or make complex charts. Excel is better suited if you’re looking to perform complex calculations or create detailed charts.

Wrapping Up

Hopefully, you are now pretty comfortable with using HLOOKUP in Excel. You can also use what you’ve learned and apply it to the VLOOKUP and XLOOKUP functions too.

If you found this guide useful and would like to support our site while also improving your productivity, consider checking out our premium templates. If you find one you like, you can use the code SSP to save 50% at checkout.

Get Premium Templates

Related:

• The Best Excel Courses to Make You a Skilled User in No Time
• How to Use VLOOKUP in Excel
• Microsoft Excel Vs Numbers: Which is the Better Spreadsheet Program?
• The Best Microsoft Excel Alternatives that are Mac Compatible
• Spreadsheets vs Database: What’s the Difference?
• Airtable vs Google Sheets: The Ultimate Comparison