The HLOOKUP guide
=HLOOKUP(B10, $B$3:$G$7, 3, FALSE) finds the country in B10 in the top row of B3:G7 and returns the value 3 rows down, counting the top row as 1. It is VLOOKUP for tables that run across the sheet.
When a table runs sideways
Rate cards, monthly reports and price grids often put the categories across the top: one column per country, month or size. VLOOKUP searches down a column, so it can't use them. HLOOKUP searches across the first row and returns from a row below it.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: what to find in the top row of the table.
- table_array: the table, starting with the row you search.
- row_index_num: which row of the table to return. The top row is 1.
- range_lookup:
FALSEfor an exact match. Leave it out and HLOOKUP does an approximate match, which assumes the top row is sorted.
Count rows from the table, not the sheet
In the challenge, the table starts in row 3 with the countries. The 5 kg prices sit in row 5 of the sheet, but they're row 3 of the table: countries (1), up to 2 kg (2), up to 5 kg (3). Writing 5 returns the 5th row of the table, the delivery days, and nothing tells you it's the wrong row. A number larger than the table, like 7 for a 5-row table, gives #REF!.
HLOOKUP or XLOOKUP?
In Excel 365 and 2021 or later, =XLOOKUP(B10, $B$3:$G$3, $B$5:$G$5) does the same job. It takes the row to return as a range, so there's no number to count, and it does an exact match by default. HLOOKUP still matters in older files and older versions of Excel, and the logic is the same as VLOOKUP's.
Common mistakes
- The sheet's row number. The row number counts from the top of the table.
- VLOOKUP on a sideways table. The countries aren't in the first column, so it finds nothing.
- Leaving out FALSE. On names that aren't in A to Z order, approximate match returns the wrong column without an error.
- An unlocked table. Filled down, B3:G7 becomes B4:G8, and the row of countries drops out. Lock it as
$B$3:$G$7.
The column version: VLOOKUP practice and filling a lookup down.
FAQ
- What is the difference between VLOOKUP and HLOOKUP?
- VLOOKUP searches down the first column of a table and returns from a column to its right. HLOOKUP searches across the first row and returns from a row below it.
- How is the row index number counted in HLOOKUP?
- From the top row of the table, which is 1, not from row 1 of the sheet. In a table that starts in row 3, sheet row 5 is row 3 of the table.
- Why does HLOOKUP return #REF!?
- The row number is larger than the number of rows in the table. A table B3:G7 has 5 rows, so 6 or more gives #REF!.
- Should I use XLOOKUP instead of HLOOKUP?
- If everyone who opens the file has Excel 365 or 2021 or later, XLOOKUP is simpler: it searches a row or a column and returns a range, with no row number to count.



































































