How to Use HLOOKUP (Horizontal Lookup) Function in Excel

The HLOOKUP function in Microsoft Excel searches for a value in the top row of a range.  It then returns a value in the same column from a row number you specify in the range.  You can tell it to find an exact match or the nearest match.

Use HLOOKUP when your comparison values are in a row across the top of a table of data, and you want to look down a specified number of rows.  Use it particularly if you want an exact match.

Alternatives:

Use VLOOKUP when your comparison values are in a column to the left of the data you want to find.  Use it particularly if you want an exact match.

Use LOOKUP (Vector form) instead if the row of comparison values is not above the row of data to return (HLOOKUP), or the column of comparison values is not left of the column of data to return (VLOOKUP), or if the comparison values and the data to return are not in the same range, or even the same worksheet.  Use it particularly if you can tolerate a nearest match, and have sorted data.

Use the INDEX function with one or two MATCH functions under the same circumstances as LOOKUP (Vector form), but you want an exact match.  For exact matches, the data need not be sorted.

LOOKUP and INDEX(MATCH) both avoid the problem where inserting a column in a VLOOKUP table, or a row in an HLOOKUP table, can corrupt the result.

HLOOKUP Syntax

HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Arguments:

Lookup_value (Required): The value to find in the first row of the table. Lookup_value can be a value, a reference, or a text string.

Table_array (Required): A range in which to look up data. Use a reference to a range, or a range name.

The values in the first row of table_array can be text, numbers, or logical values.

If range_lookup is TRUE, the values in the first row of table_array must be sorted in ascending order: …-2, -1, 0, 1, 2, …, A-Z, FALSE, TRUE. Otherwise, HLOOKUP may not give the correct value. If range_lookup is FALSE, table_array does not need to be sorted.

Uppercase and lowercase text are equivalent.

Sort the values in ascending order, left to right. For more information, see Microsoft’s Sort data in a range or table.

Row_index_num (Required): The row number in table_array from which the matching value will be returned. A row_index_num of 1 returns the first row value in table_array.  A row_index_num of 2 returns the second row value in table_array, and so on. If row_index_num is less than 1, HLOOKUP returns the #VALUE! Error.  If row_index_num is greater than the number of rows on table_array, HLOOKUP returns the #REF! error.

Range_lookup (Optional): A logical value that specifies whether you want an exact match or an approximate match. If TRUE or omitted, an approximate match is returned. In other words, if an exact match is not found, the largest value that is less than lookup_value is returned. If FALSE, HLOOKUP will look for an exact match. If it cannot find one, it gives the error value #N/A.

Remarks:

If HLOOKUP can’t find lookup_value, and range_lookup is TRUE, it uses the largest value that is less than lookup_value.

If lookup_value is smaller than the smallest value in the first row of table_array, HLOOKUP returns the #N/A error value.

If range_lookup is FALSE and lookup_value is text, you can use the wildcard characters, question mark (?) and asterisk (*), in lookup_value. A question mark matches any single character.  An asterisk matches any sequence of characters. If you want to find an actual question mark or asterisk, type a tilde (~) before the character.

Examples:

Here are some examples using the data above:

In cell A20, 1463 =HLOOKUP(2022, B3:E13, 6, FALSE): We look up 2022 in top row, and return row_Index = 6 (for Gizmo)

In cell A21, 1463 =HLOOKUP(2022, B3:E13, 6.99, FALSE): Notice that, if row_Index is not an integer, it is truncated (6.99 becomes 6).

In cell A22, 2022 =HLOOKUP(2022, $B$3:$E$13, 1, FALSE): Returning row_index=1 gives the found value.  Useful to check what an approximate match actually found.

In cell A23, #N/A =HLOOKUP(2024, $B$3:$E$13, 1, FALSE): If an exact match (of 2024) is not found, Excel returns #N/A.

In cell A24, 2022 =HLOOKUP(2024, $B$3:$E$13, 1): Here we find an approximate match of 2024 (2022). “Approximate” because we omitted range_lookup.

In cell A25, 2021 =HLOOKUP(2021.4, $B$3:$E$13, 1, TRUE ): We find an approximate match of 2021.4 at 2021. “Approximate” because range_lookup = TRUE

In cell A26, #N/A =HLOOKUP(2021.4, $B$3:$E$13, 1, FALSE ): An exact match of 2021.4 is not found. “Exact” because range_lookup = FALSE

In cell A27, #VALUE! =HLOOKUP(2022, $B$3:$E$13, 0, FALSE): A row_index < 1 produces #VALUE!

In cell A28, #REF! =HLOOKUP(2022, $B$3:$E$13, 20, FALSE): A row_index > the number of rows in table_array produces #REF

In cell A29, #N/A =HLOOKUP(2002, $B$3:$E$13, 2, FALSE). A lookup_value less than the lowest value in the top row of table_array produces #N/A.

Bonus: A Double Lookup (or, “what if you don’t always want a Gizmo?”)

You may have noticed that row_index is a fixed number in all the above examples.

What if that were variable?

I’m glad you asked!

HLOOKUP doesn’t do a second level of lookup.  No Excel function does.  What we can do is use another function, like MATCH:

In cell A32, we enter or reference the product we want, in this case, a “Doohickey”.

In cell A33, 5 =MATCH(A32, A3:A13, 0)

MATCH looks up an exact match in A3:A13. Note that we included the heading, which is OK for an exact match. If we hadn’t included the heading, we could have used = 1 + MATCH(A32, A4:A13, 0)  to give the correct row_index.  This is because the HLOOKUP table_array includes the row of headings.

In cell A34 we use A33, the result of MATCH, as the row_index: 1388 =HLOOKUP(2022, $B$3:$E$13, A33, FALSE).

Of course, you could nest the formula from A33 inside A34 instead. Thus: =HLOOKUP(2022, $B$3:$E$13, MATCH(A32,A3:A13, 0), FALSE) – but it might be difficult to debug if something goes wrong. Like looking for a misspelt “Doohicky” instead.