How to Use Excel HLOOKUP Function and Fix Problems (2024)

Video: HLOOKUP Function

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:

• 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:

• do a horizontal lookup - across the first row of the table on a worksheet.
  • The H in HLOOKUP stands for horizontal
• return a value from another row, in same column where the matching 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 Excel functions 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, with the first letter in upper case
  • NOTE: The spelling must be an exact match, but HLOOKUP is case-insensitive
  • Any combination of upper case and lower case will match, if the spelling is a match
• region lookup table has two rows, and is in range C2:F3
• list of region names is in the first row of the table
• sales total is in the 2nd row of the table.
• FALSE is used in the last argument, to find an exact match for the lookup value.

The Excel formula in cell C7, in the screenshot below, 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 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.

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.

HLOOKUP with Wildcards

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

• * - asterisk wildcard character represents any number of characters in that position, including zero characters.
• ? - question mark wildcard character represents one characters in that position

Two Asterisk Wildcards

In the example shown below, there are two asterisk wildcards in the formula, before and after the reference to cell B5:

• =HLOOKUP("*" & B5 & "*",C2:F3,2,FALSE)

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

Asterisks in Lookup Value Cell

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.

• Cookies is the first product name with the letter i, then any single character (?), then the letter s

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

1. The minus sign is an operator, so adding that calculation converts the text numbers to real numbers.
2. 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 formulasection, 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

Compare Lookup Functions

VLOOKUP Function

INDEX and MATCH Functions

Count Functions

INDIRECT Function