HLOOKUP - Search across a table's top row
Read horizontally stored freight rates while distinguishing the lookup row from the relative return-row number.
By the end, you can
- Search keys across the top row.
- Select a return row within the table.
- Handle absent zones and Excel's approximate default.
Before you start: Understand VLOOKUP's exact flag and table-relative positions.
In this lesson
The horizontal counterpart to VLOOKUP
HLOOKUP searches the first row of a selected table and returns a value from a lower row in the same column. Logistics may store zones across a heading row with freight rates underneath. If the keys instead run down a column, use VLOOKUP or XLOOKUP rather than forcing the wrong orientation.
Syntax and arguments
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])Count return rows from the top of the selected table, not from the top of the worksheet.
| Argument | Purpose | Example |
|---|---|---|
lookup_valueRequired | A key to search across the first table row. | E2 |
table_arrayRequired | The horizontal key row and the rows containing return values. | A1:D3 |
row_index_numRequired | 1-based return-row position within table_array. | 2 |
range_lookupOptional | FALSE exact; TRUE or omitted approximate in Excel. | FALSE |
Horizontal zone freight rates
150| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | North | South | East | West | Requested zone | Rate |
| 2 | 120 | 150 | 100 | 180 | South | 150 |
| 3 | 2 | 3 | 1 | 4 | West | 180 |
=HLOOKUP(E2,$A$1:$D$3,2,FALSE)South is in column B of the source table. Return its second table row: ₹150 per parcel.
Different return rows and missing zones
=HLOOKUP("East",A1:D3,3,FALSE)The third table row gives one delivery day for East.
=HLOOKUP("South",A1:D3,2,FALSE)*2Two parcels at ₹150 each cost ₹300.
=IFERROR(HLOOKUP("Central",A1:D3,2,FALSE),"Unavailable")Central is absent; the exact lookup error becomes the agreed label.
Exact versus approximate in Excel
- FALSE does not require sorted keys and returns the first exact match across the top row. Repeated zone labels can conceal a duplicate rate.
- TRUE or an omitted flag requests approximate matching in Excel. The top row must be sorted ascending; the function selects the largest key not exceeding the request.
- For a sorted numeric heading row of 0, 5,000, 10,000, a 7,500 request uses the 5,000 heading. Do not use this rule for unsorted zone names.
- A missing exact key gives #N/A, and a return row beyond the table gives #REF!. A blank return field can display as zero rather than a missing-key error.
HLOOKUP is available in older and current Excel versions. XLOOKUP can also work with aligned horizontal arrays and offers a clearer exact default in versions that support it.
Common mistakes and compatibility with Practice
Check your understanding
Choose an answer for instant feedback. You can try again; answers stay only on this page.