This Excel tutorial shows how to use HLOOKUP for horizontal data, pulling a matching value out of a table where the headers run across the top row. It works like scanning the column headings of a printed timetable and then reading straight down to the row you need. The steps below use a simple monthly sales table, but the same method fits any layout with labels in a row.
Key Takeaways
- HLOOKUP searches the first row of a table and returns a value from a row below it in the same column.
- The formula has four parts: lookup value, table array, row index number, and range lookup.
- Set the last argument to FALSE for an exact match, which is what most real-world tasks need.
- Locking the table range with absolute references keeps the formula accurate when copied to other cells.
Step-by-Step: Use HLOOKUP for Horizontal Data
The example below uses a table in A1:E3. Row 1 holds the months (Jan, Feb, Mar, Apr in B1:E1), row 2 holds Units Sold, and row 3 holds Revenue. The goal is to type a month into cell B6 and get that month’s revenue in cell B7.
Set Up the Data and the Lookup Cell
- Step 1: Arrange the data so the labels to search sit in the top row of the table. HLOOKUP always searches the first row, so the lookup values must be there.
- Step 2: Type the value to find in a separate cell, for example Mar in cell B6. Using a cell reference instead of typing the value inside the formula makes the lookup easy to change later.
- Step 3: Click the cell where the result should appear, such as B7.
Build the HLOOKUP Formula
- Step 4: Type =HLOOKUP( to start the formula.
- Step 5: Enter the lookup value by clicking cell B6, then type a comma.
- Step 6: Select the whole table, A1:E3, and type a comma. This is the table array. It must include the header row and every row the formula might return a value from.
- Step 7: Type the row index number, followed by a comma. Counting from the top row of the selected table as 1, Revenue is in row 3, so enter 3.
- Step 8: Type FALSE for the range lookup and close the bracket. The finished formula reads =HLOOKUP(B6,A1:E3,3,FALSE).
- Step 9: Press Enter. Excel finds Mar in the top row and returns the Revenue figure from the same column.
Tip: The row index number counts rows inside the selected table, not worksheet row numbers. If the table started in row 10 instead of row 1, the Revenue row would still be 3 as long as it is the third row of the selected range.
Lock the Table Range Before Copying
- Step 10: Click inside the formula, select the table reference A1:E3, and press F4 (on Windows) so it becomes $A$1:$E$3. The formula now reads =HLOOKUP(B6,$A$1:$E$3,3,FALSE).
- Step 11: Copy the formula down or across as needed. The table range stays fixed while the lookup cell adjusts.
Tip: Most people find that changing the row index number is the quickest way to reuse the same layout for different results. Switching 3 to 2 returns Units Sold instead of Revenue.
Understanding the Four HLOOKUP Arguments
Each argument has a clear job, and knowing them makes troubleshooting much faster.
- lookup_value: the value to find in the first row of the table, such as Mar or a cell reference holding it.
- table_array: the full block of cells to search, including the top row and the rows to return data from.
- row_index_num: the position of the row to return a value from, counted from the first row of the table array.
- range_lookup: FALSE (or 0) for an exact match, TRUE (or 1) for an approximate match. Leaving it blank defaults to approximate, which often gives wrong results on unsorted data.
Common Problems and Fixes
The Formula Returns #N/A
This means the lookup value was not found in the first row of the table. Check for spelling differences and extra spaces, such as ‘Mar ‘ with a trailing space in the header. Also confirm the lookup value is not sitting in a row below the first row of the selected range, because HLOOKUP only searches the top row.
The Formula Returns #REF!
The row index number is larger than the number of rows in the table array. If the table has three rows and the formula asks for row 4, Excel cannot return a value. Lower the number or widen the selected range to include more rows.
The Formula Returns #VALUE!
This appears when the row index number is less than 1, or when the lookup value is longer than 255 characters. Use a row index of 1 or higher and shorten the lookup value.
The Result Is Wrong but No Error Appears
The most likely cause is a missing FALSE at the end. Without it, Excel uses an approximate match, which needs the top row sorted in ascending order and may return a nearby value instead of the exact one. Add FALSE as the fourth argument.
Results Break After Copying the Formula
When the table range shifts as the formula is copied, lookups start pointing at the wrong cells. Lock the table array with dollar signs, as shown in Step 10, so it stays fixed.
Numbers Will Not Match
If the lookup value is a number but the headers are stored as text (or the reverse), the match fails and #N/A appears. Make sure both sides use the same data type, for example by converting text numbers to real numbers.
Frequently Asked Questions
What does HLOOKUP stand for in Excel?
HLOOKUP stands for horizontal lookup. It searches across the top row of a table and returns a value from a chosen row in the same column.
What is the syntax of the HLOOKUP function?
The syntax is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The last argument is optional, but setting it to FALSE is recommended for exact matches.
Should the last argument be TRUE or FALSE?
Use FALSE when an exact match is needed, which covers most everyday tasks such as finding a month, product code, or name. TRUE returns the closest smaller match and requires the top row to be sorted in ascending order.
Can HLOOKUP return a value from a row above the lookup row?
No. HLOOKUP searches the first row of the table array and can only return values from that row or the rows below it. The table range must start at the row that contains the lookup values.
Why does HLOOKUP return #N/A when the value is clearly in the table?
The usual causes are hidden spaces, spelling differences, or a mismatch between text and numbers. The value must also be located in the first row of the selected table array.
Is HLOOKUP case-sensitive?
No. HLOOKUP treats uppercase and lowercase letters the same, so mar and MAR both match Mar.
Can wildcards be used with HLOOKUP?
Yes, when the range lookup is set to FALSE and the lookup value is text. The question mark matches a single character and the asterisk matches any number of characters, so Ma* can match Mar.
How do the row index numbers work?
The row index number is the position of the row inside the selected table array, starting at 1 for the top row. It is not the worksheet row number.
How can the same HLOOKUP formula be reused for several results?
Lock the table array with absolute references using F4, then copy the formula and change the row index number or the lookup cell. The table range stays fixed while the other values adjust.
