How to Use HLOOKUP in Excel [An Easy 2023 Guide]

Basically, to use HLOOKUP in Excel, just enter the function name, lookup value, table array, row index, and range. Need more help? This guide will show you how to use this function with some examples. If you want to know more, read on.

What Is HLOOKUP in Excel?

HLOOKUP is a convenience function in Excel that allows you to look up a defined value in the top row of a table and retrieve the corresponding value from a column in the specified row. The “H” in HLOOKUP stands for horizontal, meaning the function searches rows horizontally in the table. This function works very much like the VLOOKUP function, which searches columns vertically.

HLOOKUP Excel Syntax

Before discussing HLOOKUP formulas in Excel with examples, here is the syntax for HLOOKUP formulas:

=HLOOKUP(lookup value, 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 number, text string, or cell reference.
• table_array – This required value defines the table from which the function looks for data. This parameter can be a range name or a reference to a range. The value in the first row of this parameter can be numeric, string, or boolean
• row_index – This is a required parameter used to define the number of rows for which matching values ​​are retrieved. This parameter defines the row number of the table_array parameter.
• range – This is an optional parameter that allows you to enter a logical value to specify whether to search for exact matches or close matches. A value of TRUE or empty gives an approximate match. If the value is FALSE, the function searches for an exact match. If no exact match is found, the function returns a #N/A error.

There are a few things you should know about HLOOKUP expressions:

1. If the Row_index parameter is less than or equal to 0, Excel’s HLOOKUP function returns #VALUE! error.
2. HLOOKUP in Excel returns #REF! if the Row_index parameter is greater than the total number of rows error.
3. If the Range parameter is TRUE, the values ​​in the first row must be added in ascending order. Otherwise the function will not output the correct value.
   1. Numbers must be written as -1, 0, 1, 2.
   2. The text should be written A, B, C, D
   3. Logical expressions must be written as FALSE, TRUE

1. If the HLOOKUP function can find lookup_val and range is TRUE, the maximum value less than lookup_val is used.
2. If the range is defined as FALSE and the lookup_val parameter is a text value, you can use wildcard characters such as asterisk (*) and question mark (?) in the lookup_val parameter.
   1. An asterisk (*) can be used to match any sequence of characters.
   2. A question mark (?) can be used to match a single character.

If you’re using Google Sheets, check out our guide on how to use HLOOKUP in Sheets.

How to Use HLOOKUP in Excel

Now that you understand the syntax of HLOOKUP formulas in Excel, let’s look at some examples of actual formulas.

Finding an Exact Match HLOOKUP Example

This example uses the HLOOKUP function to find an exact match in a table. The columns in this table show dates, and the top row shows some of the items that have been sold. Find out how many “notebook” items were sold on the second day. Here’s how to find an exact match using HLOOKUP:

1. Open your workbook and click the cell where you want to enter the formula.
2. Enter the starting segment of the HLOOKUP expression =HLOOKUP(. After entering the first part, you will see the required part of the expression
3. d parameter.
4. Enter the value you want to search for as the first parameter. In this example, we want to find out the sales of notebooks on a particular day. Enter “Notebook” for the first argument. Be sure to enclose string parameters in quotation marks before adding them.
5. Add a comma (,) to separate the parameters.
6. In the second argument, write the address of the table to look up the value. In this case the table will be B1:G8. Because the first row of the table can contain strings, numbers, or logical values, the second parameter can contain the header bar of the table. Add another comma to separate the parameters.
7. add a cell whose matching value is taken as the third parameter. In this example, we write 3 because the value is in the third cell from the top of the table_array parameter. Add a comma to separate the parameters.
8. finally, add the optional range parameter. In this example, we are looking for an exact match, so entering 0 or FALSE here causes the HLOOKUP function to find an exact match for the “Notebook” parameter.
9. Add parentheses to complete the expression and press Enter to execute the expression.

Finding an Approximate Match HLOOKUP Example

suppose you have a table with words that can have different spellings. For example, “jewelry” can be spelled as “jewellery”. In this case, you can use the range parameter to force the function to search for an approximate match. In this example, we misspell jewelry and use HLOOKUP to find an approximate match.

In this case, the process of using the HLOOKUP expression is the same as in the previous example. Here’s how to find an approximate match using HLOOKUP:

1. Open the workbook that uses the HLOOKUP formula and enter the first part =HLOOKUP(.
2. Add the string to search for as the first parameter. In this example, I wrote “Joolly”. Add a comma and write the table address (B16:G24.
3. Add another comma and write the value you want to get. In this example, write 2.
4. Here, as the last parameter, write a scope parameter that specifies whether to search for close matches or exact matches. In this case, look for a putative match that writes 1 or TRUE.
5. Finally, add parentheses to complete the formula and press Enter to run it.

Many people use both spreadsheets and Excel. Here’s how to convert a spreadsheet to Excel and vice versa.

HLOOKUP From Another Worksheet

Multiple worksheets let you organize your data into smaller sections. This is especially useful if you want to hide the actual table of data. This example uses two worksheets. The second worksheet contains the data from running the formulas in the first worksheet.

Here’s how to use HLOOKUP to find a value from another worksheet:

1. Open the workbook that uses the HLOOKUP formula.

1. To go to a worksheet, click the sheet name in the bottom footer bar. In this sample workbook, we want to use the formulas from Sheet1.

1. Now click on the cell where you want to enter the formula and enter the first segment of the HLOOKUP formula =HLOOKUP(.
2. The first parameter has a cell containing the string value of the lookup value. Stored in cell F2. Add a comma to separate the parameters.
3. Add the address of the table from the second worksheet to the second parameter. To do this, write the name of the sheet, add an exclamation mark (!) and the address of the table. Add a comma to separate the two parameters. In this example the parameters are Sheet2!A1:G9.
4. Write the third parameter to define the cell from which to get the value. In this example we want to get the value from 5.
5. finally, add the optional range parameter. This example requires an exact match, so write 0 here.
6. Add a closing parenthesis to complete the formula and press Enter to run it.

Google Sheets and Microsoft Excel are very similar in functionality. However, these tools can differ significantly from each other when looking at some of the more complex features. Here is a detailed comparison of spreadsheets and Excel.

Reasons Why the HLOOKUP Function May Not Work

Excel formulas are very specific and the function may not work unless you add the parameters in the proper format. Reasons for HLOOKUP not working include:

• Lookup_val parameter does not exist in first line.
• You didn’t use proper references when writing the formula.
• Lookup_val exceeds 255 characters.
• A formula parameter contains extra spaces. Use the TRIM function to remove extra spaces.
• If the lookup_val parameter is a string, enclose it in quotes.

Microsoft recently added a new function to Excel called XLOOKUP. This feature is currently not available in Google Sheets. Here are some alternatives to XLOOKUP in spreadsheets.

Frequently Asked Questions

What Does HLOOKUP Do?

The HLOOKUP formula is a function in Google Sheets and Microsoft Excel that allows users to look up values ​​in the top row of a table and return the corresponding value from a user-specified row in that table.

What Is the HLOOKUP Formula?

The HLOOKUP expression syntax is =HLOOKUP(lookup_val, table_array, row_index, range) . This formula requires three parameters to work. The lookup_val parameter defines the value the function looks for in the first row of the table. The table_array parameter defines the address of the table from which the function looks for data. The row_index argument defines the row position from which matching values ​​are retrieved.-

What Are the Differences Between Sheets and Excel?

Using Google Sheets and Excel has various advantages and disadvantages. Google Sheets is free for individuals and small businesses, while Microsoft Excel is paid. However, Google Sheets doesn’t have many data visualization options, while Microsoft Excel has a nice charting feature set. Microsoft Excel has advanced features, but Google Sheets formulas are less customizable.

Can Sheets Do Everything that Excel Can?

Both spreadsheets and Excel work similarly. However, spreadsheets are more suitable for people who don’t want to do a lot of complicated calculations or create complicated charts. Excel is a better choice if you want to perform complex calculations or create detailed graphs.

Wrapping Up

I hope you’ve gotten pretty comfortable with using HLOOKUP in Excel. You can also use what you have learned and apply it to the VLOOKUP and XLOOKUP functions as well.

If you found this guide helpful and want to support your site while increasing your productivity, consider checking out our premium templates. If you find something you like, use code SSP to get 50% off at checkout.