HLOOKUP is the horizontal version of VLOOKUP. It searches across the top row of a table and returns a value from a row below.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Example
A table has months in B1:M1 across the top, and sales in row 2. To find sales for the month typed in P1:
=HLOOKUP(P1, B1:M2, 2, FALSE)
Excel finds the month in the top row and returns the value from row 2 of the table.
Arguments
- row_index_num: which row to return, counted from the top of the table (the first row is 1).
- range_lookup:
FALSE for exact match, TRUE for approximate (top row must be sorted).
HLOOKUP vs VLOOKUP
Use VLOOKUP when your data runs down in columns, and HLOOKUP when the headings run across the top. Most data is vertical, so VLOOKUP is more common.
A better option
XLOOKUP handles both directions with one syntax and does not need a row number: =XLOOKUP(P1, B1:M1, B2:M2).
Worked example
Months are across row 1 (B1:M1) and sales in row 2. To get the sales for the month typed in P1: =HLOOKUP(P1,A1:M2,2,FALSE). The 2 means return from the second row of the range.
Common mistakes
- Forgetting FALSE, so a wrong approximate match is returned.
- Wrong row number. It counts from the top row of the range.
- Using HLOOKUP on data in columns. Use VLOOKUP or XLOOKUP.
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
HLOOKUP searches across the top row of a table. VLOOKUP searches down the first column.
Can I use XLOOKUP instead?
Yes. XLOOKUP works on rows and columns with the same formula.
Why do I get #N/A?
The lookup value is not in the first row, or has extra spaces.
Practice this online, free
Reading is only half of learning Excel. Cellgrids is a free place for online Excel practice: solve Excel formula exercises in a live spreadsheet, prepare for Excel interview questions, and work through business case studies built for data analytics and business analytics roles.
Practice these formulas: HLOOKUP, VLOOKUP, XLOOKUP.
Start Excel practice → Case studies
