Excel HLOOKUP Function

HLOOKUP searches the first row of a table for a value, then returns data from any row below it. It's the horizontal counterpart to VLOOKUP, built for tables where categories run left to right across columns instead of down rows. In Excel 365 and Excel 2021, XLOOKUP replaces it entirely, handles both directions with one function, and doesn't force you to count rows by hand. You'll still see HLOOKUP in older workbooks, so it's worth knowing how it works. Just don't reach for it in anything new.

Syntax

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

Parameter	Required	Description
lookup_value	Yes	The value you want to find. HLOOKUP searches for this in the first row of table_array.
table_array	Yes	The table to search. HLOOKUP always looks in the first row of this range.
row_index_num	Yes	Which row to return a value from, counted from the top of table_array. Row 1 is the header row you're searching. Row 2 is the first data row below it.
range_lookup	No	FALSE or 0 for exact match. TRUE or 1 for approximate match. Defaults to TRUE if omitted — which is almost always wrong.

Basic Example

A sales report tracks quarterly targets across columns. Product names run across row 1, and targets are in row 2.

=HLOOKUP("Widget A", B1:E3, 2, FALSE)

// "Widget A"  — the product name to find in row 1
// B1:E3       — the table to search
// 2           — return the value from row 2 (targets)
// FALSE       — exact match only

If "Widget A" appears in column C, this returns the value in C2. Change 2 to 3 to pull from row 3 instead.

How HLOOKUP Works

It always searches the first row

HLOOKUP looks for lookup_value in the top row of table_array only. It can't search any other row. If your lookup values aren't in the first row, HLOOKUP won't find them.

Row index counts from the top of the table

row_index_num counts rows starting at 1 from the top of table_array — not from row 1 of the worksheet. If your table starts in row 5, row_index_num = 2 still means the second row of the table (row 6 of the worksheet).

It only looks downward

HLOOKUP returns data from rows below the search row. It can't return data from a row above the first row. If your table layout requires looking up, XLOOKUP handles that without any workaround.

Approximate match requires a sorted first row

When range_lookup is TRUE, HLOOKUP assumes the first row is sorted in ascending order left to right. If the row isn't sorted, results are unpredictable. Always use FALSE unless you specifically need approximate matching on a sorted row.

Common Use Cases

Pull a value from a horizontal lookup table

=HLOOKUP(A2, $B$1:$F$3, 2, FALSE)

// A2          — the category to look up
// $B$1:$F$3   — locked reference so it doesn't shift when copied
// 2           — second row of the table
// FALSE       — exact match

Lock table_array with $ signs when copying the formula down. Without them the range shifts and returns wrong rows.

Return different rows with the same lookup

=HLOOKUP($A2, $B$1:$F$5, 2, FALSE)   // row 2 — Q1 target
=HLOOKUP($A2, $B$1:$F$5, 3, FALSE)   // row 3 — Q2 target
=HLOOKUP($A2, $B$1:$F$5, 4, FALSE)   // row 4 — Q3 target

Keep lookup_value and table_array locked with $. Only row_index_num changes per formula.

Match a product code using a wildcard

A pricing sheet lists SKUs across the header row, and the exact code isn't always known in full. To find the price for any SKU starting with "TSH" — a shirt line with several size variants:

=HLOOKUP("TSH*", B1:J3, 2, FALSE)

// "TSH*"  — wildcard match, any SKU starting with TSH
// B1:J3   — product table, SKUs across the header row
// 2       — row 2 holds price
// FALSE   — required for wildcards to work

Handling Errors

#N/A — the lookup value wasn't found in the first row. Check spelling, data type, and leading or trailing spaces.

#REF! — row_index_num is larger than the number of rows in table_array. If your table has 3 rows and you ask for row 4, HLOOKUP can't return anything.

#VALUE! — row_index_num is less than 1. Row numbers start at 1, not 0.

Wrong result, no error — range_lookup is TRUE or omitted. Set it to FALSE.

=IFNA(HLOOKUP(A2, B1:F3, 2, FALSE), "Not found")

Notes & Gotchas

Why does HLOOKUP return the wrong value when I know the item exists?

The most common cause is range_lookup defaulting to TRUE. Add FALSE as the last argument and the result will correct itself. If it still returns wrong data, check for spaces or mismatched data types between the lookup value and the first row.

Can HLOOKUP search a row other than the first?

No. HLOOKUP always searches row 1 of table_array. If your lookup values are in a different row, restructure the table or use XLOOKUP, which lets you specify both the search row and the return row independently.

Does HLOOKUP work with wildcards?

Yes, when range_lookup is FALSE. Use * for any sequence of characters and ? for a single character. Use ~* or ~? to search for a literal asterisk or question mark.

What happens when two columns have the same value in row 1?

HLOOKUP returns the data from the first matching column it finds, scanning left to right. Duplicate headers in row 1 will always return data from the leftmost match.

Should I use HLOOKUP or XLOOKUP?

Use XLOOKUP if you're on Excel 365 or 2021. It's less fragile, requires no row counting, and defaults to exact match. Use HLOOKUP only when maintaining existing workbooks or supporting Excel 2019 and earlier.

Related Functions

Function	Use this when...
XLOOKUP	You're on Excel 365 or 2021 and want a cleaner, more flexible lookup in any direction.
VLOOKUP	Your table is oriented vertically with lookup values in the first column instead of the first row.