Excel HLOOKUP Function
The HLOOKUP function 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
Video: HLOOKUP Function
The HLOOKUP function can find an exact match in the lookup row, or it can find the closest match (approximate match). This short video shows both types of HLOOKUP:
- Exact Match: Find sales total in a specific region, with region names in first row
- Approximate Match: Find interest rate for a specific date, with dates in first row
When to Use HLOOKUP
Use the HLOOKUP function when you need to:
- look for a value horizontally - across a row on the worksheet. The H in HLOOKUP stands for horizontal
- return a value from another row, in same column where the value was found
- use a version of Excel that does not have the newer lookup function, XLOOKUP (link to Microsoft site)
Note: If you need to look for a value vertically, down a column, use VLOOKUP or XLOOKUP instead.
How to Use HLOOKUP
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.
HLOOKUP Syntax
The HLOOKUP function has the following syntax, with 3 required arguments, and 1 optional argument:
- HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
NOTE: Arguments in square brackets are optional
Required Arguments
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.
Optional Argument
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:
- Omit: If you omit this argument, HLOOKUP looks for an approximate match
- TRUE: Find an approximate match for the lookup value. Values in the first row must be sorted in ascending order, from left to right. Note: Instead of TRUE, you can enter a 1 (one)
- FALSE: Find an exact match for the lookup value. Note: Instead of FALSE, you can enter a 0(zero)
HLOOKUP Warnings
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:
- If possible, use a lookup table that is sorted by the values in the first row, in ascending order, from left to right, and use an approximate match, instead of an exact match.
- Use the MATCH function or the COUNTIF function, to check for the value first, to make sure it is in the table's first row.
- Use faster functions, such as INDEX and MATCH, or XLOOKUP, to return values from a table.
HLOOKUP Exact Match
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:
- region name is entered in cell B7
- region lookup table has two rows, and is in range C2:F3
- sales total is in row 2 of the table.
- FALSE is used in the last argument, to find an exact match for the lookup value.
The formula in cell C7 is:
=HLOOKUP(B7,C2:F3,2,FALSE)
If the region name is not found in the first row of the lookup table, the HLOOKUP formula result is #N/A
HLOOKUP Approximate Match
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:
- a date is entered in cell C5
- the rate lookup table has two rows, and is in range C2:F3
- the lookup table is sorted by the Date row, in ascending order
- rate is in row 2 of the table.
- TRUE is used in the last argument, to find an approximate match for the lookup value.
The formula in cell D5 is:
=HLOOKUP(C5,C2:F3,2,TRUE)
If the exact date is not found in the first row of the lookup 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, so the value for January 1st (0.25) is returned.
HLOOKUP with 2 Criteria
In the previous examples, the lookup table had 2 rows:
- Lookup values in row 1
- Values to return in row 2
In those HLOOKUP formulas, the row index number (2) was typed into the formula as the 3rd argument. For example:
- =HLOOKUP(B7,C2:F3,2,FALSE)
Calculate the Row Index Number
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:
- There is a column for each of the 4 regions
- There are two rows for the item names - Desks and Chairs
- Quantity sold is entered for each region and product
- There are 2 criteria cells: Region (B7) and Item (C7).
Find Correct Row for Selected Item
If we always wanted the chair quantity, its row number (3) could be typed into the HLOOKUP formula:
- =HLOOKUP(B7,C2:F4,2,FALSE)
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.
- MATCH(C7,B2:B4,0)
Here is the formula in cell D7, where the HLOOKUP result is based on 2 criteria, with MATCH in the 3rd argument:
- =HLOOKUP(B7,C2:F4,MATCH(C7,B2:B4,0),FALSE)
Tip: To learn more about the MATCH function, and see other examples of how to use it, go to the INDEX and MATCH page.
HLOOKUP and COUNTIF
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:
- The Region names in the table heading row are sorted A-Z
- The HLOOKUP function uses an approximate match (FALSE)
- The COUNTIF function checks for the value first, to make sure it is in the table's first row.
Here is the lookup table, with this HLOOKUP / COUNTIF formula in cell C5:
- =IF(COUNTIF(C2:F2,B5),HLOOKUP(B5,C2:F3,2,TRUE),"Not Found")
How the HLOOKUP and COUNTIF Formula Works
1) First, the COUNTIF function counts the number of times that "Central" is found in cells C2:F2
- COUNTIF(C2:F2,B5)
- NOTE: This is a short version of checking if the count is greater than zero.
- You could use the longer version, to make that part of the formula easier to understand: COUNTIF(C2:F2,B5)>0
- COUNTIF result is either TRUE (count greater than zero) or FALSE (count NOT greater than zero)
2) Next, the IF function checks the result of the COUNTIF function
3) Then, the IF function returns its result:
- If the COUNTIF result was FALSE, the IF result is "Not Found"
- If the COUNTIF result was TRUE, the result of the HLOOKUP function is shown.
HLOOKUP Errors
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:
#VALUE!
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.
#REF!
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.
#N/A
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.
Problem: Match Dates or Numbers
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.
- Note: You might have the same problem matching numbers, with the HLOOKUP function.
See why this problem occurs, and how to make a simple change to the HLOOKUP formula, to fix the problem
Lookup Table With Dates
In this example, there is a lookup table, with
- Dates in the heading row
- Sales targets and bonus % for each date
This list is formatted as an Excel table, and it is named RatesLU.
HLOOKUP Formula With Dates
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]
- Target: =HLOOKUP(B8,RatesLU[#All],2,TRUE)
- Bonus %: =HLOOKUP(B8,RatesLU[#All],3,TRUE)
Tip: You can learn more about structured table references on the Microsoft site.
HLOOKUP Formula Errors
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.
HLOOKUP Problem with Dates
In this example, there is a problem with date matching, because:
- Date in cell B8 is stored as a NUMBER
- Dates in the lookup table heading row are TEXT values
Excel treats number dates and text dates as different values, even if they look the same.
Table Headings Are Always Text
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.
- Warning: Excel automatically formats dates and numbers as TEXT, if they are in a named table's heading row. This change is made silently, without any notification.
Fix HLOOKUP Problem with Dates
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:
- Target: =HLOOKUP(B8, --RatesLU[#All],2,TRUE)
- Bonus %: =HLOOKUP(B8, --RatesLU[#All],3,TRUE)
How It Works
- The minus sign is an operator, so adding that calculation converts the text numbers to real numbers.
- The second minus sign changes the results to positive numbers
Other HLOOKUP Problems
If this solution did not fix your HLOOKUP problem, try the suggestions in the Troubleshoot the VLOOKUP formula section, on the VLOOKUP page.
For example,
- One of the values may contain leading spaces (or trailing, or embedded spaces), and the other doesn't.
- One of the values may contain hidden characters, copied from a web site, and the other doesn't.
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.
Download the Sample File
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.
More Functions Tutorials
Last updated: November 4, 2021 12:38 PM