How to use HLOOKUP in Excel with formula examples

Excel HLOOKUP syntax and uses

The HLOOKUP function in Excel has the following arguments:

• Lookup_value (required) - the value to search for. It can be a cell reference, numeric value or text string.
• Table_array (required) - two or more rows of data in which the lookup value is searched. It can be a regular range, named range or table. Lookup values should always be located in the first row of table_array.
• Row_index_num (required) - the row number in table_array from which the value should be returned. For example, to return the matching value from the 2^nd row, set row_index_num to 2, and so on.
• Range_lookup (optional) - a logical (Boolean) value that instructs HLOOKUP to search with exact or approximate match.

  If TRUE or omitted, an approximate match is returned. What it means is if an exact match is not found, your Hlookup formula will do a non-exact match and return the next largest value that is less than lookup_value.

  If FALSE, only an exact match is returned. If no value in a specified row matches the lookup value exactly, HLOOKUP throws the #N/A error.

To make things easier to understand, you can translate Excel's HLOOKUP syntax:

into normal English:

To see how it works in practice, let's make a simple Hlookup example. Supposing you have a table with some basic information about the planets of our Solar system (please see the screenshot below). What you want is a formula that returns the diameter of the planet whose name is entered in cell B5.

In our Hlookup formula, we will be using the following arguments:

• Lookup_value is B5 - the cell containing the planet name you want to find.
• Table_array is B2:I3 - the table where the formula will look up the value.
• Row_index_num is 2 because Diameter is the 2^nd row in the table.
• Range_lookup is FALSE. Because the first row of our table is not sorted from A to Z, we can only look up with exact match, which works just fine in this example.

Now you put the arguments together and get the following formula:

=VLOOKUP(40, A2:B15,2)

3 things you should know about Excel HLOOKUP function

Whenever you do a horizontal lookup in Excel, please remember the following facts:

1. The HLOOKUP function can only search in the top-most row of table_array. If you need to look up somewhere else, consider using an Index / Match formula.
2. HLOOKUP in Excel is case-insensitive, it does not distinguish uppercase and lowercase.
3. If range_lookup is set to TRUE or omitted (approximate match), the values in the first row of table_array must be sorted in ascending order (A-Z) left to right.

Horizontal lookup with approximate and exact match

As you already know, the HLOOKUP function in Excel can perform a lookup with exact and non-exact match depending on which value is supplied to the range_lookup argument:

• TRUE or omitted - approximate match
• FALSE - exact match

Please keep in mind that although we say "approximate match", any Hlookup formula searches for an exact match in the first place. But setting the last argument to FALSE allows the formula to return an approximate match (the nearest value that is less than the lookup value) if an exact match is not found; TRUE or omitted returns the #N/A error in this case.

To better illustrate the point, consider the following HLOOKUP examples.

How to do HLOOKUP from another worksheet or workbook

In general, h-lookup from another sheet or a different workbook means nothing else than supplying external references to your HLOOKUP formula.

To pull out matching data from a different worksheet, you specify the sheet name followed by an exclamation mark. For example:

=HLOOKUP(B$1, Diameters!$B$1:$I$2,2,FALSE)

If the worksheet name contains spaces or non-alphabetical characters, enclose the name in single quotation marks, like this:

=HLOOKUP(B$1, 'Planet diameters'!$B$1:$I$2,2,FALSE)

When referencing another workbook, include the workbook name enclosed in square brackets:

=HLOOKUP(B$1, [Book1.xlsx]Diameters!$B$1:$I$2, 2, FALSE)

If you are pulling data from a closed workbook, the entire path should be specified:

=HLOOKUP(B$1, 'D:\Reports\[Book1.xlsx]Diameters'!$B$1:$I$2, 2, FALSE)

Excel HLOOKUP with partial match (wildcard characters)

As is the case with VLOOKUP, Excel's HLOOKUP function allows using the following wildcard characters in the lookup_value argument:

• Question mark (?) to match any single character
• Asterisk (*) to match any sequence of characters

Wildcards come in handy when you want to pull information from a database based on some text that is part of the lookup cell's contents.

For example, you have a list of customer names in row 1 and order IDs in row 2. You want to find the order id for a specific customer but you cannot remember the customer name exactly, though you do remember it begins with "ace".

Assuming your data are in cells B1:I2 (table_array) and order numbers are in row 2 (row_index_num), the formula goes as follows:

=HLOOKUP("ace*", B1:I2, 2, FALSE)

To make the formula more flexible, you can type the lookup value in a special cell, say B4, and concatenate that cell with the wildcard character, like this:

=HLOOKUP(B4&"*", B1:I2, 2, FALSE)

1. HLOOKUP in Excel cannot look above itself

Even if you forget all other details about horizontal lookup in Excel, please remember this essential one - Hlookup can only search in the top-most row of the table. If case your lookup values reside in some other row, an N/A error is returned. To overcome this limitation, use an INDEX MATCH formula.

2. Approximate match vs. exact match

When doing a lookup in Excel, either horizontal (Hlookup) or vertical (Vlookup), in most cases you'd be searching for a particular thing, and therefore require an exact match. When searching with approximate match (range_lookup set to TRUE or omitted), remember to sort values in the first row in ascending order.

For more information and formula examples, please see Excel Hlookup with approximate and exact match.

3. The table array reference changes when copying the formula

When using multiple HLOOKUPs to retrieve information about a row of lookup values, you have to lock the table_array reference as demonstrated in Absolute and relative cell references in Hlookup formulas.

4. Inserting or deleting a new row

To understand why inserting a new row can break an Hlookup formula, remember how Excel HLOOKUP gets information about the lookup value - based on the row index number you specify.

Supposing, you want to get sales figures based on the product ID. Those figures are in row 4, so you type 4 in the row_index_num argument. But after a new row has been inserted, it becomes row 5... and your Hlookup stops working. The same problem may occur when deleting an existing row from the table.

The solution is either lock the table to prevent your users from inserting new rows, or use INDEX & MATCH instead of Hlookup. In Index/Match formulas, you specify the rows to look up and to return values from as range references, not index numbers, and Excel is smart enough to adjust those references on the fly. So, you are free to delete or insert as many columns and rows as you want without worrying about updating every formula in your worksheet.

5. Duplicates in the table

The HLOOKUP function in Excel can only return one value, which is the first value in the table that matches the lookup value.

If there are a few identical records in your table, choose one of the following solutions that best suits your needs:

• Remove duplicates by using Excel's means or our Duplicate Remover
• If duplicate records should be kept in the dataset, create a PivotTable to group and filter your data the way you want.
• Use an array formula to extract all duplicate values in the lookup range.

6. Extra spaces

When your obviously correct Hlookup formula returns a bunch of #N/A errors, check your table and lookup value for extra spaces. You can quickly remove leading, trailing and excess in-between spaces by using Excel TRIM function or our Cell Cleaner tool.

7. Numbers formatted as text

Text strings that look like numbers are another stumbling block for Excel formulas. The detailed description of this issue and possible solutions are described in Why Excel formulas may stop working.

8. Lookup value exceeds 255 characters

All Lookup functions in Excel work only as long as a lookup value is under 255 characters. A longer lookup value results in the #VALUE! error. Since the INDEX /MATCH formula is free of this limitation, use it to overcome this hurdle.

9. Full path to the lookup workbook is not specified

If you perform h-lookup from another workbook, remember to supply the full path to it. A couple of formula examples can be found here: How to do Hlookup from another worksheet or workbook.

10. Wrong arguments

It has already been highlighted more than once that HLOOKUP is a demanding function that should be treated with great care. Below are a few most common errors caused by supplying incorrect arguments:

• If row_index_num is less than 1, the HLOOKUP function returns the #VALUE! error.
• If row_index_num is greater than the number of rows in table_array, #REF! error is returned.
• If you search with approximate match and your lookup_value is smaller than the smallest value in the first row of table_array, the #N/A error is returned.