Excel HLOOKUP Function: Syntax, Examples and Tips

Part of the free Module 5: Excel Formulas and Functions · Function 33 of 105 · Full Excel course

The Excel HLOOKUP function searches for a value in the top row of a table and returns a value from a specified row in the same column. Use HLOOKUP in Excel when your data runs horizontally, with months, years or categories as column headers. It returns the matched value or #N/A when nothing matches.

Table of Contents

HLOOKUP syntax

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

Arguments

Argument	Required	Meaning
lookup_value	Required	The value to find in the first row of table_array.
table_array	Required	The range containing the header row and the data rows below it.
row_index_num	Required	The row within table_array to return from. 1 is the header row itself, 2 is the row beneath it, and so on.
range_lookup	Optional	FALSE for exact match. TRUE or omitted for approximate match, which requires the first row sorted left to right in ascending order.

Step-by-step example

A budget sheet has months across B1:M1 and expense categories down the rows, so B1:M5 is the table. To return the Rent (row 3 of the table) for the month typed in A8:

=HLOOKUP(A8, B1:M5, 3, FALSE)

Excel scans B1:M1 from left to right for the month in A8.
When it finds the column, it moves down to the 3rd row of the range.
It returns that cell. If the month is not found, the result is #N/A.

The screenshot below shows HLOOKUP reading a horizontal table where the lookup value is matched in the header row.

Excel HLOOKUP function example returning a value from a horizontal table by matching the header row — HLOOKUP Formula Example

Practical use cases

1. Lock the table when filling across

=HLOOKUP(B$8, $B$1:$M$5, 2, FALSE)

2. Dynamic row number with MATCH

=HLOOKUP(A8, $B$1:$M$5, MATCH("Rent", $A$1:$A$5, 0), FALSE)

The row is chosen by name, so inserting rows does not break the formula.

3. Approximate match for tiers

With sales thresholds 0, 10000, 25000, 50000 in B1:E1 and commission rates in B2:E2:

=HLOOKUP(G2, B1:E2, 2, TRUE)

4. Hide #N/A

=IFERROR(HLOOKUP(A8, $B$1:$M$5, 3, FALSE), "")

Alternatives: INDEX-MATCH works horizontally too: =INDEX($B$3:$M$3, MATCH(A8, $B$1:$M$1, 0)). In Excel 365, =XLOOKUP(A8, B1:M1, B3:M3) replaces HLOOKUP with no row number and exact match by default.

Common mistakes and errors

#N/A – the value is not in the first row, or an omitted FALSE caused an approximate match on unsorted headers. Also check for spaces and text-formatted numbers or dates.
#REF! – row_index_num is greater than the number of rows in table_array.
#VALUE! – row_index_num is less than 1 or is text.
Using HLOOKUP on vertical data – if your lookup values are in a column, use VLOOKUP instead.
Header dates versus text – a header “Jan-25” stored as a real date will not match the text “Jan-25”. Keep both sides the same type.

Tips and best practices

Freeze the header row (View > Freeze Panes) so the lookup labels stay visible while you check results across wide tables.
Keep headers as one data type. Month headers should all be real dates or all text; mixing them causes intermittent #N/A.
Combine HLOOKUP with MATCH for the row so both the column and the row are found by name, making the formula immune to inserted rows.
Consider restructuring: if you constantly fight HLOOKUP, transpose the data (Paste Special > Transpose) and use VLOOKUP or a PivotTable instead.
Name the table (Formulas > Define Name) so formulas read HLOOKUP(A8, BudgetTable, 3, FALSE).

Related functions

VLOOKUP – the vertical equivalent.
INDEX and MATCH – flexible replacement that works in any direction.
LOOKUP – simple approximate lookup across a row or column.
TRANSPOSE – flips a horizontal table to vertical if you prefer VLOOKUP.
Browse the Excel Formulas hub.

Frequently asked questions

What is the difference between HLOOKUP and VLOOKUP?

HLOOKUP searches the first row of a table and returns from a row below; VLOOKUP searches the first column and returns from a column to the right. Choose based on whether your headers run across or down.

Why does HLOOKUP return #N/A?

The lookup value does not exactly match any header cell, often because of spaces, different data types, or an omitted FALSE argument on unsorted data.