HLOOKUP: Horizontal Lookup (2025)
Quick Answer: HLOOKUP searches horizontally: =HLOOKUP('Q2', A1:E10, 5, FALSE) finds 'Q2' in row 1, returns row 5 value. Like VLOOKUP but across rows. Use when headers are in top row (months, quarters, years). XLOOKUP is better alternative for Excel 365.
What is HLOOKUP?
HLOOKUP (Horizontal Lookup) searches for a value in the top row of a table and returns a value from a specified row below. It's the horizontal counterpart to VLOOKUP.
Visual Example:
| → | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| Sales | $10,000 | $12,000 | $15,000 | $18,000 |
| Profit | $2,000 | $2,500 | $3,200 | $4,000 |
| Growth | 5% | 8% | 12% | 15% |
=HLOOKUP("Q2", A1:E4, 3, FALSE)Finds "Q2" in row 1, returns row 3 value = $2,500
HLOOKUP vs VLOOKUP:
VLOOKUP (Vertical)
- • Searches DOWN columns
- • Headers in FIRST COLUMN
- • Most common layout
- • Example: Employee list
HLOOKUP (Horizontal)
- • Searches ACROSS rows
- • Headers in FIRST ROW
- • Less common layout
- • Example: Monthly reports
When to Use HLOOKUP:
✓ Calendar-Style Data
Months/quarters across top row
✓ Time-Series Reports
Years or periods in first row
✓ Dashboard Summaries
Categories arranged horizontally
✓ Financial Statements
Columns for different periods
How HLOOKUP Works:
- 1. Search top row: Looks for lookup value in first row
- 2. Find column: Identifies which column contains the match
- 3. Move down: Goes down specified number of rows
- 4. Return value: Returns the value at that intersection
💡 Modern Alternative
Excel 365 users: Consider XLOOKUP instead. It's more flexible, can search any direction, and has better error handling.
HLOOKUP still useful for: Compatibility with older Excel versions and when data is specifically structured horizontally.
HLOOKUP Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Arguments
lookup_value (required)
Value to search for in the first row of table
table_array (required)
Table of data with lookup values in first row
row_index_num (required)
Row number to return value from (1 = first row)
range_lookup (optional)
TRUE = approximate match, FALSE = exact match
TRUE (default): Finds closest match, requires sorted data
Common Patterns
Monthly Report Lookup
=HLOOKUP("March", A1:M5, 3, FALSE)
Find March, return row 3 value
Quarter Sales
=HLOOKUP("Q2", QuarterlyData, 2, FALSE)
Use named range for clarity
Dynamic Column Search
=HLOOKUP(A2, Data, 5, FALSE)
Lookup value from cell reference
With Error Handling
=IFERROR(HLOOKUP(A2, Data, 3, FALSE), "Not Found")
Graceful error handling
Row Index Numbering
| Row in Table | row_index_num | Description |
|---|---|---|
| First row (headers) | 1 | The lookup row itself |
| Second row | 2 | First data row |
| Third row | 3 | Second data row |
⚠️ Important Notes
- • First row only: HLOOKUP only searches first row of table_array
- • Case insensitive: "March" = "MARCH" = "march"
- • Exact match recommended: Always use FALSE unless data is sorted
- • #N/A error: Value not found in first row
- • #REF! error: row_index_num larger than table height
- • Static references: Insert/delete rows can break formula
- • XLOOKUP alternative: More flexible for Excel 365 users
10+ HLOOKUP Examples
Basic HLOOKUP
1. Monthly Sales Lookup
=HLOOKUP("March", A1:M5, 2, FALSE)Find March column, return row 2 sales
2. Quarterly Revenue
=HLOOKUP("Q2", A1:E10, 3, FALSE)Find Q2 quarter, return row 3 value
3. Year Comparison
=HLOOKUP(2024, A1:F5, 4, FALSE)
Find 2024 column, return row 4 data
With Cell References
4. Dynamic Month Lookup
=HLOOKUP(A2, MonthlyData, 3, FALSE)
Lookup month from cell A2
5. Variable Row Index
=HLOOKUP("Q1", A1:E10, B2, FALSE)Row number from cell B2
6. Named Range
=HLOOKUP(A2, QuarterlyReport, 5, FALSE)
Use named range for clarity
Advanced Applications
7. With Error Handling
=IFERROR(HLOOKUP(A2, Data, 3, FALSE), "Not Found")
Display custom message if not found
8. Sum Multiple Lookups
=HLOOKUP("Q1", A1:E5, 2, 0) + HLOOKUP("Q2", A1:E5, 2, 0)Combine multiple quarters
9. Percentage Change
=(HLOOKUP(2025, A1:F5, 2, 0) - HLOOKUP(2024, A1:F5, 2, 0)) / HLOOKUP(2024, A1:F5, 2, 0)
Calculate year-over-year growth
Practical Scenarios
10. Price List Lookup
=HLOOKUP(A2, PriceTable, 2, FALSE)
Find product price by size/category
11. Region-Based Commission
=HLOOKUP(Region, A1:F10, 3, FALSE) * Sales
Get commission rate, multiply by sales
🎯 Pro Tips
- • Always use FALSE: Exact match prevents unexpected results
- • Named ranges: Make formulas easier to read and maintain
- • IFERROR wrapper: Graceful handling of #N/A errors
- • Consider XLOOKUP: More flexible if you have Excel 365
- • Absolute references: Use $A$1:$M$10 when copying formula
- • Document structure: Keep first row headers stable
Frequently Asked Questions
What's the difference between HLOOKUP and VLOOKUP?
VLOOKUP searches vertically (down columns), lookup value in first column. HLOOKUP searches horizontally (across rows), lookup value in first row. Use VLOOKUP for typical data with headers in leftmost column. Use HLOOKUP for calendar/time-series layouts with headers in top row. VLOOKUP is more common (90%+ of cases).
Why do I get #N/A error?
Value not found in first row of table_array. Check: (1) Spelling matches exactly (case-insensitive but spaces matter), (2) Lookup value exists in first row, (3) Using FALSE for exact match, (4) No hidden characters in data. Use IFERROR to handle gracefully: =IFERROR(HLOOKUP(...), 'Not Found').
Can HLOOKUP search in columns other than the first row?
No, HLOOKUP only searches the first row. It's designed specifically for horizontal tables with headers in row 1. If you need to search a different row, restructure your data or use INDEX/MATCH which is more flexible for complex lookups.
When should I use TRUE vs FALSE for range_lookup?
Almost always use FALSE (exact match). TRUE (approximate match) requires sorted data and finds closest value ≤ lookup value. Use TRUE only for: tax brackets, commission tiers, grading scales. FALSE is safer and returns #N/A if not found instead of wrong value.
How do I use HLOOKUP with dynamic ranges?
Three approaches: (1) Named range that auto-expands: =HLOOKUP(A2, DynamicRange, 3, FALSE), (2) OFFSET formula: =HLOOKUP(A2, OFFSET(A1,0,0,5,COUNTA(1:1)), 3, FALSE), (3) Excel Tables: =HLOOKUP(A2, Table1, 3, FALSE). Tables are recommended - they auto-expand and have built-in structure.
Should I use HLOOKUP or XLOOKUP in Excel 365?
Use XLOOKUP if available. XLOOKUP benefits: (1) Searches any row/column, (2) Default exact match (no FALSE needed), (3) Better error handling, (4) Can return multiple columns, (5) Search right-to-left. Use HLOOKUP only for: compatibility with older Excel versions, or if data structure specifically requires horizontal lookup.