HLOOKUP in Excel
Last updated
HLOOKUP searches the first row of a table for a value, then returns the value from a row you choose in the same column. For example, =HLOOKUP("April",A1:G5,3,FALSE) finds April among the months in row 1 and returns the amount from the 3rd row of the table. Use it when your headers run across the top, and end with FALSE for an exact match.
HLOOKUP syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
- lookup_value
- The value to find in the first row of the table, such as a month or a cell that holds it.
- table_array
- The whole table, e.g.
A1:G5. HLOOKUP always searches its first row. - row_index_num
- Which row of the table to return, counting from its first row:
1is the row you search,3is two rows below it. - range_lookup
- Optional.
FALSE(or0) for an exact match.TRUE, or leaving it out, finds the largest value less than or equal to lookup_value and needs the first row sorted in ascending order.
Practice HLOOKUP here
Type your formula straight into the sheet. It’s checked like real Excel, and you can write it with commas or semicolons.
Find a budget for one month
In B7, write an HLOOKUP that returns the Groceries budget for April, using the budget in A1:G5. The months run across row 1.
| Category | January | February | March | April | May | June |
| Rent | 1,200.00 | 1,200.00 | 1,200.00 | 1,250.00 | 1,250.00 | 1,250.00 |
| Groceries | 410.00 | 385.00 | 432.00 | 398.00 | 420.00 | 445.00 |
| Transport | 95.00 | 110.00 | 88.00 | 120.00 | 102.00 | 97.00 |
| Utilities | 180.00 | 165.00 | 150.00 | 128.00 | 112.00 | 104.00 |
| Groceries in April |
How to use HLOOKUP, step by step
- Click the cell where you want the result and type
=HLOOKUP(. - Click the cell that holds the value to find, then type a comma.
- Select the whole table, starting with the row to search. Press F4 to lock it with $ if you will copy the formula. Type a comma.
- Type the row number to return, then
,FALSE)for an exact match, and press Enter.
HLOOKUP examples
Budget for one month
=HLOOKUP("April",$A$1:$G$5,3,FALSE)
Finds April in row 1 and returns the 3rd row of the table, the groceries budget.
Month from a cell
=HLOOKUP(B7,$A$1:$G$5,4,FALSE)
Looks for the month typed in B7, so the result updates when you change B7.
Shipping rate by weight band
=HLOOKUP(B5,$B$1:$G$3,3,TRUE)
Row 1 holds the lower limit of each band, sorted ascending. TRUE returns the band that the weight in B5 falls into.
Show a message instead of #N/A
=IFERROR(HLOOKUP(B7,$A$1:$G$5,3,FALSE),"Not found")
IFERROR replaces #N/A when the month isn’t in row 1.
Common HLOOKUP errors and how to fix them
| Result | Cause | Fix |
|---|---|---|
| #N/A | The value isn’t in the first row of the table: a typo, extra spaces, or a number stored as text. Or the table moved when you copied the formula. | Check the value, use TRIM, lock the table with $, or wrap the formula in IFERROR. |
| #REF! | row_index_num is larger than the number of rows in table_array, e.g. 6 for A1:G5. | Count the rows of the table again, or select a taller table. |
| #VALUE! | row_index_num is less than 1. | Use 1 or more. Row 1 is the row HLOOKUP searches. |
| Wrong value | range_lookup is TRUE or missing, and the first row isn’t sorted. HLOOKUP returns another column’s value without any error. | End the formula with FALSE (or 0) for months, codes and names. |
HLOOKUP vs VLOOKUP vs XLOOKUP
HLOOKUP is VLOOKUP turned on its side: VLOOKUP searches the first column of a table and returns a column to its right, HLOOKUP searches the first row and returns a row below it. Both need an index number and FALSE for an exact match. XLOOKUP covers both directions with a lookup range and a return range, matches exactly by default, and can return a row above the one it searches, but it exists only in Excel 2021, 2024 and Microsoft 365. HLOOKUP works in every version.
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
They work the same way in different directions. VLOOKUP searches down the first column and returns a column number. HLOOKUP searches across the first row and returns a row number.
Why does HLOOKUP return the wrong value?
Usually because the last argument is missing. Without FALSE, HLOOKUP uses approximate match and assumes row 1 is sorted. Write =HLOOKUP(B7,A1:G5,3,FALSE).
Can HLOOKUP return a row above the lookup row?
No. It always searches the first row of table_array and returns rows below it. Use XLOOKUP, or INDEX and MATCH, to return a row above.
Should I use HLOOKUP or XLOOKUP?
If everyone opening the file has Excel 2021, 2024 or Microsoft 365, XLOOKUP is simpler: =XLOOKUP(B7,B1:G1,B3:G3). HLOOKUP works in every version, so you’ll keep meeting it in shared files.
Keep practising
All 5 HLOOKUP exercises, with explanations and progress tracking.
Open the HLOOKUP practice set