Lookup & reference
How to Use HLOOKUP in Excel (Search Across a Row)
HLOOKUP is the horizontal cousin of VLOOKUP. It searches across the top row of a table for a value, then returns a result from a row beneath it, which is perfect for data laid out left to right such as months or quarters across columns.
You will reach for it when your headers run horizontally instead of down a column. This guide covers the exact syntax, real worked examples, and how to fix the #N/A and #REF! errors that catch people out.
HLOOKUP syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) | Argument | Required | What it does |
|---|---|---|
lookup_value | Required | The value to search for, in the top row of the table. |
table_array | Required | The range that holds the data. The lookup row must be the top row. |
row_index_num | Required | Which row of the table to return, counting from 1 at the top row. |
range_lookup | Optional | FALSE for an exact match (what you usually want); TRUE/omitted for an approximate match on a row sorted left to right. |
Note: HLOOKUP is the horizontal version of VLOOKUP. Works the same in Google Sheets, where the fourth argument is called is_sorted.
Examples
Real, copy-paste-ready formulas.
1. Exact match: find a quarter's value by header
=HLOOKUP(B8, A1:E5, 4, FALSE) Searches the top row of A1:E5 for the quarter in B8 and returns the value from the 4th row of the table. FALSE forces an exact match, so a missing header returns #N/A instead of the wrong column.
2. Handle "not found" gracefully
=IFERROR(HLOOKUP(B8, A1:E5, 4, FALSE), "Not found") Wrapping HLOOKUP in IFERROR replaces the #N/A with a friendly message, which is ideal for dashboards and shared reports.
3. Pick the right row dynamically
=HLOOKUP(B8, A1:M5, C8, FALSE) Feeding a cell into row_index_num lets one formula return different rows. Keep the number between 1 and the table height or HLOOKUP throws #REF!.
How to write HLOOKUP step by step
- 1
Click the cell where you want the result and type =HLOOKUP(
- 2
Click the cell holding the value you are searching for (the lookup_value), then type a comma.
- 3
Select the whole table range, starting with the row that contains the lookup value. Press F4 to lock it with $ signs if you will copy the formula.
- 4
Type the row number to return, counting from the top of your table (1, 2, 3…), then a comma.
- 5
Type FALSE for an exact match, then close the bracket ) and press Enter.
Common errors and fixes
| Error | Why it happens | How to fix it |
|---|---|---|
#N/A | The lookup value is not in the top row, often due to extra spaces or a number stored as text. | Use exact match (FALSE), clean values with TRIM(), and make sure both sides are the same data type. Wrap in IFERROR for a friendly fallback. |
#REF! | row_index_num is greater than the number of rows in table_array. | Lower the row number, or extend table_array down so it includes the row you want to return. |
#VALUE! | row_index_num is less than 1, or an argument is malformed. | Make sure the row index is a positive whole number and the arguments are comma-separated correctly. |
Wrong value returned | range_lookup was omitted or TRUE on an unsorted row, so HLOOKUP grabbed the nearest match. | Add FALSE as the fourth argument to force an exact match. |
Frequently asked questions
What is the difference between HLOOKUP and VLOOKUP?
HLOOKUP searches horizontally across the top ROW and returns a value from a row below it; VLOOKUP searches vertically down the first COLUMN and returns a value from a column to the right. Use HLOOKUP when your headers run across columns.
Why is my HLOOKUP returning #N/A?
The most common reasons are: the lookup value is not in the top row, there are trailing spaces, or a number is stored as text on one side. Use exact match (FALSE), TRIM() to remove spaces, and confirm both values are the same type.
Can HLOOKUP look upward?
No. HLOOKUP can only return rows below the lookup row. To return a row above it or to look in any direction, use XLOOKUP or INDEX/MATCH instead.
Does HLOOKUP work in Google Sheets?
Yes. The syntax is identical; the optional fourth argument is named is_sorted, where FALSE means an exact match.