HLOOKUP in Excel (and When to Use It)

HLOOKUP finds a value in the top row of a table and returns something from a row below it. Use the HLOOKUP Excel function when your table runs sideways, with the labels across the top, like a rate card or a price grid. It works in every version of Excel. This guide is for beginners who know a little VLOOKUP, or who met a sideways table in a job test.

HLOOKUP Excel syntax, part by part#

HLOOKUP has four parts, and the last one decides exact or approximate match:

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

• lookup_value: what you want to find in the top row, like a zone code.
• table_array: the whole table, including the top row you search.
• row_index_num: which row of the table to bring back. Row 1 is the top row of the table.
• range_lookup: FALSE (or 0) for an exact match. TRUE (or 1), or leaving it out, gives an approximate match.

The "H" stands for horizontal. HLOOKUP is VLOOKUP turned on its side: VLOOKUP searches down the first column and counts columns to the right, while HLOOKUP searches across the first row and counts rows down. If you know one, you know the other. Our VLOOKUP function guide covers the shared ideas in more detail.

How to use HLOOKUP: a shipping rate card example#

Here is a rate card in B1:F4, with zones across the top:

The shipments are listed below: SH-301 is going to zone Z3 (in B7) and weighs 12 kg (in C7). To get its base fee, type this in D7:

=HLOOKUP(B7,$B$1:$F$4,2,FALSE)

HLOOKUP searches the top row for Z3, finds it in column D, then goes down to row 2 of the table and returns 9.00. The $ signs lock the table so it stays in place when you fill the formula down with Ctrl+D (Mac: ⌘D).

Change only the row number to get the other values:

Because HLOOKUP returns a number, you can use it in a calculation. The shipping cost is the base fee plus the weight times the per-kg rate:

=HLOOKUP(B7,$B$1:$F$4,2,FALSE)+C7*HLOOKUP(B7,$B$1:$F$4,3,FALSE)

For SH-301 that is 9 + 12 × 1.45 = 26.40. Excel multiplies before it adds, so you don't need brackets.

HLOOKUP vs VLOOKUP: what's the difference?#

They are the same function in two directions. Pick the one that matches how your table is laid out.

Most business data runs down the page, one record per row, so VLOOKUP is far more common. HLOOKUP shows up with rate cards, monthly summaries with months across the top, and tables exported from other tools.

Using HLOOKUP with an approximate match#

With TRUE as the last part, HLOOKUP finds the largest value in the top row that is less than or equal to your number. That is useful for bands that run across a row, such as volume discounts:

=HLOOKUP(35,$B$1:$E$2,2,TRUE) returns 5%, because 35 falls in the band that starts at 10. The top row must be sorted from smallest to largest, left to right, or the result can be wrong without any error. The VLOOKUP approximate match guide explains this rule in more depth; it applies to HLOOKUP in exactly the same way.

Why does HLOOKUP return #N/A or #REF!?#

Each error points to a different problem:

• #N/A means the value wasn't found in the top row. Check the spelling, look for extra spaces (TRIM removes them), and make sure numbers aren't stored as text. With TRUE, #N/A also means your number is smaller than the first value.
• #REF! means the row number is bigger than the table. B1:F4 has 4 rows, so row 5 doesn't exist.
• A wrong number with no error usually means a missing FALSE, so HLOOKUP did an approximate match on an unsorted row.
• The table moved. If you forget the $ signs, the table slides down as you fill and loses the top row. The lower rows then show #N/A.

How to make HLOOKUP survive inserted rows#

A typed row number breaks when someone inserts a row inside the table. If a new "Fuel Surcharge" row goes in above Per kg, your 3 now points at the surcharge, and every shipping cost is wrong with no error. You can let MATCH find the row number instead:

=HLOOKUP(B7,$B$1:$F$4,MATCH("Per kg",$A$1:$A$4,0),FALSE)

MATCH looks for the label "Per kg" in the row labels and returns its position, 3. Because the label range starts on row 1, the same row as the table, the position lines up with HLOOKUP's row count. Insert a row, and MATCH finds the new position by itself. The same trick works with VLOOKUP, using MATCH on the header row for the column number.

When to use HLOOKUP (and when not to)#

Use HLOOKUP when the file must work in any version of Excel, or when a test asks for it by name. Otherwise, two newer options are safer, because they don't use a typed row number that breaks when someone inserts a row.

XLOOKUP (Excel 2021 or Microsoft 365) searches a row as easily as a column. Give it the top row to search and the row to return:

=XLOOKUP(B7,$B$1:$F$1,$B$2:$F$2)

It defaults to an exact match, so you can't forget FALSE. Read XLOOKUP in Excel: everything a beginner needs to learn the rest of its options.

INDEX MATCH works in every version. MATCH finds the column of the zone, and INDEX returns that position from the Base Fee row:

=INDEX($B$2:$F$2,MATCH(B7,$B$1:$F$1,0))

See how INDEX MATCH works for a full walk-through. And when you need to match both a row label and a column header, such as a zone and a fee type at once, use a two-way lookup with INDEX and two MATCHes.

Practise it#

Reading about lookups is not the same as typing them. In ExcelReady's HLOOKUP lesson, you work through the shipping rate card from this guide in a live spreadsheet: find base fees, lock the table, return the per-kg rate and delivery days, and build the full shipping cost formula from two HLOOKUPs. Try the plan exercise on this page, then practise HLOOKUP on a sideways rate card.