Excel HLOOKUP Function (Horizontal Lookup) – Examples & Practice
Practice the Excel HLOOKUP function online with an interactive grid, instant feedback, and clear formula help.
Instruction
Find "P-102" in the top row of A1:C6 and return the value from the 3rd row with exact match.
Formula Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value: Value to find in the first row of table_array.
- table_array: Range containing the header row and data rows.
- row_index_num: Which row inside table_array to return (1 = header row itself).
- [range_lookup]: FALSE exact; TRUE approximate on sorted headers.
What it does
HLOOKUP looks for a value in the top row of a table and returns a value from a specified row below in the same column. Use it when your reference data is organized horizontally instead of vertically.
Excel HLOOKUP Function Examples
Exact match on a horizontal header
=HLOOKUP("P-102", A1:C6, 3, FALSE)
Finds P-102 in the first row of A1:C6 and returns the value from the 3rd row.
Approximate band on sorted headers
=HLOOKUP(E1, A1:F2, 2, TRUE)
Use only when the header row is sorted appropriately for approximate logic.
Literal lookup value
=HLOOKUP(B1, A1:D5, 4, FALSE)
Uses the value in B1 as the key in the top row.
lookup-reference-table.xlsx
| A | B | C | |
|---|---|---|---|
| 1 | Code | Name | Price |
| 2 | P-101 | Mouse | 19 |
| 3 | P-102 | Keyboard | 45 |
| 4 | P-103 | Monitor | 199 |
| 5 | P-104 | USB Hub | 29 |
| 6 | P-105 | Headset | 59 |
| 7 | Output |
Input Formula
Need Help?
Tips
- Prefer vertical tables plus VLOOKUP/XLOOKUP when you can normalize data.
- Document row indexes next to the formula during model handoffs.
- Validate that header spelling matches keys exactly.
HLOOKUP Function Use Cases
- Tables laid out horizontally (dates or metrics as columns)
- Matrix-style scorecards where headers are in row 1
- Legacy models that cannot be pivoted quickly
- Teaching the mirror image of VLOOKUP
- Quick reads from wide reference blocks
Common mistakes - HLOOKUP function not working
- Confusing
row_index_numwith Excel row numbers on the sheet - Using
TRUEwithout a sorted header row - Selecting a table_array that does not include the return row
- Mixing merged cells in the header row
- Expecting leftward lookups (use XLOOKUP instead)
FAQ
How is HLOOKUP different from VLOOKUP?
HLOOKUP scans the top row of a table and returns a value from a lower row. VLOOKUP scans the first column and returns from a column to the right.
When is row_index_num wrong?
It counts rows within table_array from the top of that range. Inserting rows above the range changes relative positions—verify after layout edits.
Should I use FALSE for range_lookup?
Use FALSE for exact matches on codes or labels. TRUE is for approximate match on sorted numeric bands.
Can HLOOKUP return values above the header row?
No. The return row must be below the lookup row inside the same table_array.
Is HLOOKUP still recommended?
It is valid for legacy layouts, but XLOOKUP is usually simpler for new workbooks.
Comparison
| Function | Scans | Returns from |
|---|---|---|
| HLOOKUP | Top row | Lower row |
| VLOOKUP | First column | Right column |
| XLOOKUP | Flexible | Flexible |
Example
=HLOOKUP("P-102", A1:C6, 3, FALSE)
Advanced examples
HLOOKUP + IFERROR
=IFERROR(HLOOKUP("X-404", A1:D6, 2, FALSE), "Unknown")
Keeps dashboards readable when a key is missing.
Pair with named ranges
Name the table_array so formulas read like =HLOOKUP(B1, ProductMatrix, 3, FALSE) for easier audits.