How to Use HLOOKUP in Excel
The horizontal twin of VLOOKUP: searches across the top row of a table and returns a value from a row below it.
What is HLOOKUP in Microsoft Excel?
HLOOKUP works like VLOOKUP rotated 90 degrees. It scans the first row of a table for your value, then drops down a fixed number of rows to return the answer. It suits data laid out with periods or categories across the top, such as a monthly P&L or a rate card.
Formula Syntax
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| lookup_value | Required | The value to find in the first row of the table. |
| table_array | Required | The table to search. The first row is scanned for the lookup value. |
| row_index_num | Required | Which row of the table to return, counting the first row as 1. |
| range_lookup | Optional | FALSE or 0 for exact match, TRUE or 1 for approximate match on sorted data. Always pass FALSE for lookups. |
When to Use HLOOKUP (Real-World Business Scenarios)
- 1
Pulling a value from a rate card where months or product tiers run across the top row.
- 2
Reading a single figure from a financial statement laid out horizontally.
- 3
Legacy workbooks that already use horizontal layouts and cannot be restructured.
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Metric | Jan | Feb | Mar |
|---|---|---|---|
| Revenue | 45000 | 52000 | 38000 |
| Cost | 30000 | 31000 | 29000 |
| Profit | 15000 | 21000 | 9000 |
How it works: Finds 'Feb' in the header row, then returns the value from row 3 of that column, which is February profit.
Common Errors & How to Fix Them
Why it happens: range_lookup was omitted, so Excel used approximate matching and could not find the value.
Fix: Pass FALSE as the fourth argument: =HLOOKUP("Feb",B1:D3,3,FALSE).
Why it happens: row_index_num is larger than the number of rows in table_array.
Fix: Count the rows in the table including the header and use a number within that range.
Try HLOOKUP Live in Your Browser
No need to launch desktop Excel. Practice formulas with real-time feedback in our HyperFormula sandbox.
Frequently Asked Questions (HLOOKUP)
What does the HLOOKUP formula do in Excel?
HLOOKUP works like VLOOKUP rotated 90 degrees. It scans the first row of a table for your value, then drops down a fixed number of rows to return the answer. It suits data laid out with periods or categories across the top, such as a monthly P&L or a rate card.
What is the syntax for HLOOKUP?
What are common mistakes when using HLOOKUP?
#N/A: range_lookup was omitted, so Excel used approximate matching and could not find the value. (Pass FALSE as the fourth argument: =HLOOKUP("Feb",B1:D3,3,FALSE).) #REF!: row_index_num is larger than the number of rows in table_array. (Count the rows in the table including the header and use a number within that range.)
Related Excel Formulas
Keep building your formula toolkit with these lookup & reference functions.
- XLOOKUPThe modern, versatile replacement for VLOOKUP and HLOOKUP that searches in any direction without column index numbers.
- VLOOKUPThe industry-standard vertical lookup function that searches the first column of a table and returns a value in the same row.
- INDEX & MATCHThe robust two-part lookup formula that looks left, looks up, and never breaks when new columns are inserted.
- MATCHReturns the position of a value inside a range — the engine behind INDEX MATCH and most advanced lookups.
- CHOOSEPicks one value from a list by position — a compact alternative to nested IF for fixed options.
- XMATCHThe modern replacement for MATCH with exact-match default, reverse search and wildcard support built in.
Master HLOOKUP in Intermediate Excel
Learn HLOOKUP along with our formula library, practice files, and verifiable certificates in our complete curriculum.