The HLOOKUP function in Microsoft Excel looks for a value horizontally, across a row, to find an exact match, or the closest match. Then, it returns a value from another row, in same column where it found the value. HLOOKUP is similar to VLOOKUP, which is used for vertical lookups
The Excel HLOOKUP function can find an exact match in the lookup row, or it can find the closest match (approximate match). This short video tutorial shows both types of HLOOKUP:
Use the HLOOKUP function when you need to:
Note: If you need to look for a value vertically, down a column, use Excel functions VLOOKUP or XLOOKUP instead.
Each function in Excel has a syntax -- the list of arguments the function needs, the order for those arguments, and whether each argument is required or optional.
When creating a formula, Excel automatically shows a function's syntax after you type its name, and the opening bracket.
The HLOOKUP function has the following syntax, with 3 required arguments, and 1 optional argument:
NOTE: Arguments in square brackets are optional
Here are the 3 required arguments for HLOOKUP, in the order they need to be entered:
1. lookup_value: What value should HLOOKUP look for, in the first row of the lookup range (table_array). This argument can be a value that you type into the formula, or a cell reference, for a cell that contains the lookup value.
2. table_array: Where is the lookup table that has the lookup values in the first row? This can be a range reference, or a range name, or a table name, with 2 or more columns.
3. row_index_num: Within the lookup table (table_array), which row has the values that HLOOKUP should return? This number can be different from the worksheet row number, if the lookup table does not start in row 1 on the worksheet.
Here is the optional argument for HLOOKUP, and its order in the function's syntax:
4. [range_lookup]: Should HLOOKUP find an exact match or an approximate match? There are three options for entering this argument:
The HLOOKUP function can be slow, especially when looking for an exact match for a text string, in an unsorted table. For better results, try these suggestions:
The HLOOKUP function looks for a value in the top row of the lookup table. In this example, we'll find the sales total for a selected region. We want an exact match for the Region name, so the following settings are used:
The Excel formula in cell C7, in the screenshot below, is:
If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A
Usually, an exact match is required when using HLOOKUP, but sometimes an approximate match works better. In this example, rates change at the start of each quarter, and those dates are entered as column headings.
With HLOOKUP set for an approximate match, you can find the rate that was in effect for any date. In this example:
The formula in cell D5 is:
If the exact date is not found in the top row of a table, the HLOOKUP formula returns the rate for the next largest date that is less than lookup_value.
The lookup value in this example is March 15th. That date is not in the date row (topmost row), so the value for January 1st (0.25) is returned.
In the previous examples, the lookup table had 2 rows:
In those HLOOKUP formulas, the row index number (2) was typed into the formula as the 3rd argument. For example:
In some lookup tables, there might be multiple rows below the heading row.
Instead of typing a row index number in the HLOOKUP function's arguments, you can use another function to calculate the correct row number, instead of typing it into the formula.
In this product sales quantity lookup table:
If we always wanted the chair quantity, its row number (3) could be typed into the HLOOKUP formula:
In this case though, we want to find the correct row index number, based on the Item name in cell C7. To do that, use the MATCH function to find the selected item's position in cells B2:B4.
Here is the formula in cell D7, where the HLOOKUP result is based on 2 criteria, with MATCH in the 3rd argument:
Tip: To learn more about the MATCH function, and see other examples of how to use it, go to the INDEX and MATCH page.
As mentioned in the HLOOKUP Warnings section (above), the HLOOKUP function can be slow, especially if you're looking for an exact match for a text string, in an unsorted table.
This example uses these methods for making HLOOKUP work faster:
Here is the lookup table, with this HLOOKUP / COUNTIF formula in cell C5:
1) First, the COUNTIF function counts the number of times that "Central" is found in cells C2:F2
2) Next, the IF function checks the result of the COUNTIF function
3) Then, the IF function returns its result:
Occasionally, an HLOOKUP formula returns an error value, instead of the result that you expected. Here are some of the error values, and what they mean:
If the row index number is less than 1, HLOOKUP returns the #VALUE! error value.
You might get that error if the row_index_number argument refers to a cell on the worksheet, and that cell is empty, or contains a zero.
If the row index number is greater than the number of rows in the lookup table, HLOOKUP returns the #REF! error value.
You might get that error if rows were deleted from the lookup table, and the row_index_number argument was not updated.
If a match for the lookup value is not found, the #N/A error value is returned.
You might get that error if there is a typo in the lookup value, or if some values are real dates or numbers, and the other values are text dates or numbers. See the example in the next section - Problem: Match Dates or Numbers.
In addition to looking for specific text values, you can also use wildcards with the HLOOKUP function.
The following wildcard characters in Excel represent unknown characters, before, between, or after, other characters
In the example shown below, there are two asterisk wildcards in the formula, before and after the reference to cell B5:
The formula finds the first product name that contains the letter, or string of letters, typed in cell B52, and returns that product's sales amount
Another option is to put wildcards in the reference cell that has the lookup value.
For example, type i?p in cell B5, the the formula returns the sales amount for the Cookies product.
In this example, the HLOOKUP function has a problem matching dates. Even though the dates are in the lookup table heading row, Excel returns an error, because it can't match the dates.
See why this problem occurs, and how to make a simple change to the HLOOKUP formula, to fix the problem
In this example, there is a lookup table, with
This list is formatted as an Excel table, and it is named RatesLU.
Below the lookup table, there are 2 cells with HLOOKUP formulas, to find the Target (row 2), and the Bonus% (row 3) for the date entered in cell B8.
The formulas use a structured table reference for the lookup table: RatesLU[#All]
Tip: You can learn more about structured table references on the Microsoft site.
Even though the date in cell B8 looks the same as the date in cell D3, both HLOOKUP formulas show an #N/A error, because Excel could not find the lookup value in the heading row.
In this example, there is a problem with date matching, because:
Excel treats number dates and text dates as different values, even if they look the same.
Originally, the dates in row 3 were entered as real dates (numbers). However, when the lookup table was formatted as a named Excel table, they automatically changed to text.
The HLOOKUP formula needs a small change, so Excel can see the heading dates (text) as real dates (number), and find a match for the lookup date (number).
In each formula, type two minus signs (double unary) in front of the lookup table reference:
If this solution did not fix your HLOOKUP problem, try the suggestions in the Troubleshoot the VLOOKUP formula section, on the VLOOKUP page.
On the VLOOKUP page, you'll see how to fix those problems, using functions or macros. These two functions are similar, and these troubleshooting tips might help solve your HLOOKUP problems too.
To see the formulas used in these examples, download the HLOOKUP function sample workbook. The zipped Excel file is in xlsx file format, and does not contain any macros.
Last updated: May 29, 2022 3:28 PM