HLOOKUP Generator

How HLOOKUP works

Availability: Every version of Excel, Excel for the web and Google Sheets. XLOOKUP, which supersedes it, needs Excel 2021 or Microsoft 365. Microsoft now lists HLOOKUP as a compatibility function, but it is not deprecated and existing formulas keep working.

HLOOKUP searches across the first row of a range for a value, then returns whatever sits a given number of rows below the cell it matched. It is the sideways twin of VLOOKUP: use it when the labels you are searching for run left to right along a header row - months, quarters, years, size codes, price breaks - and the figures you want run down the columns beneath them.

The two arguments people misjudge are the third and the fourth. row_index_num counts down from the first row of table_array, not from row 1 of the worksheet: if the table starts at B4, an index of 3 returns a value from sheet row 6, and an index of 1 returns the header cell you have just matched. range_lookup controls how the match is made, and its default is the dangerous one. Omit it, or pass TRUE, and Excel does an approximate match that assumes the header row is sorted in ascending order from left to right; on an unsorted header it does not error, it simply returns a value from the wrong column. Pass FALSE for exact matching unless you deliberately want banded lookup on a sorted row of thresholds.

HLOOKUP can only look down. The search row must be the first row of the range, and the answer must be at or below it, so a layout where the figures sit above the labels needs INDEX with MATCH or XLOOKUP instead. Exact matching also compares on type: a header stored as the text "2026" will not match the number 2026, and a trailing space produces #N/A on data that looks identical on screen.

If you have Excel 2021 or Microsoft 365, XLOOKUP does the same job with fewer sharp edges - it defaults to exact matching, takes the return row as a range rather than a counted offset so inserting rows cannot break it, and has a built-in if_not_found argument. HLOOKUP is still worth knowing: it is in every version, and it is what you will find in workbooks other people built.

Syntax

More worked examples

Common mistakes

Counting row_index_num from the worksheet instead of the table

With a table_array of $B$4:$M$9, an index of 5 returns sheet row 8, not row 5. Everything is measured from the top of the range. Count down from the header row you can see highlighted when you select table_array.

Leaving range_lookup out

The default is approximate. On an unsorted header row that returns a value from a neighbouring column with no error at all, so the report is wrong and looks fine. Type FALSE every time unless you are deliberately doing a banded lookup on sorted thresholds.

Inserting a row breaks the index

row_index_num is a hard-coded number, so adding a row inside the table silently shifts what it points at, and every formula using that index now returns the wrong measure. Replace the number with =MATCH($A5, $A$1:$A$9, 0) so the index follows a label, or move to XLOOKUP.

#N/A on headers that look identical

Exact matching distinguishes the text "2026" from the number 2026, and treats trailing spaces as part of the value. Test with =HLOOKUP(A2, table, 1, FALSE) to see whether the key matches at all, and clean both sides with TRIM or by converting the header row to numbers.

Needing a value above the header row

HLOOKUP can never return anything above the row it searched, and it can never search any row but the first of the range. If the labels sit below the figures, or the range would have to start above the labels, use INDEX with MATCH - =INDEX($B$1:$M$1, MATCH(A2, $B$3:$M$3, 0)) - which has no such restriction.