Excel Tutorials · Lesson 63
Not every lookup runs down a column. When your headers sit across the top row and the data runs sideways, HLOOKUP is the function built for it. This lesson covers the four arguments, the row counting rule that catches everyone, and an honest look at when XLOOKUP is the better answer.
The same free Excel Xpert companion workbook used in the video, one tab per lesson. No login, no email required.
HLOOKUP searches for a value in the top row of a range, then returns a value from a row further down in the same column. The H stands for horizontal. Its syntax is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), and it is the exact mirror image of VLOOKUP, which searches down the first column and returns a value from a column across.
The distinction is entirely about how your data is laid out, not what it contains. If your labels run down the left side and your records run in rows, use VLOOKUP. If your labels run across the top and each record occupies a column, use HLOOKUP. Most business data is vertical, which is why VLOOKUP is famous and HLOOKUP is barely known, but horizontal layouts show up constantly in budget sheets with months across the top, comparison matrices, and rate cards.
The rule that trips people up is how the row index is counted. It is not the row number on the worksheet. It counts rows within your selected range, where the header row you are searching is row 1, the row beneath it is 2, and so on. Selecting a range that starts on worksheet row 5 does not change that, the first row of the range is still row 1 for counting purposes.
The honest framing: if you have Excel 2021, Excel 2024 or Microsoft 365, XLOOKUP does this job better in every respect. It searches any direction, does not need a counted index number, and defaults to exact match. HLOOKUP remains worth knowing because it appears in thousands of existing workbooks you will inherit, and because older versions of Excel do not have XLOOKUP at all.
Step-by-step, matching the video above.
The values you want to search must live in the top row of the range, with the data you want to retrieve sitting in the rows underneath. If your labels run down a column instead, you need VLOOKUP.
In the cell where you want the answer, type =HLOOKUP(. Excel displays the four arguments as a tooltip, with the first three in bold because they are required.
Click the cell holding what you are searching for, or type it in quotation marks if it is literal text, then add a comma. This is the value HLOOKUP hunts for in the top row.
Drag across the entire block including the header row you are searching. Press F4 immediately to lock it with dollar signs, as in $B$1:$G$5, so the range does not shift when you copy the formula.
Count down from the header row within your selected range, where the header itself is 1. If the value you want is two rows below the header, the row_index_num is 3.
Type FALSE or 0 for the last argument to require an exact match. Leaving it blank defaults to TRUE, which returns approximate matches and is almost never what you want.
Press Enter, then drag or fill the formula across to the other cells that need it. Because the table array is locked with F4, every copy searches the same range.
| Range Row | B | C | D | E |
|---|---|---|---|---|
| 1 Month | Jan | Feb | Mar | Apr |
| 2 Revenue | 48,000 | 52,500 | 61,200 | 58,900 |
| 3 Expenses | 39,400 | 41,100 | 44,800 | 43,200 |
| 4 Net | 8,600 | 11,400 | 16,400 | 15,700 |
=HLOOKUP("Mar",$B$1:$E$4,3,FALSE) returns 44,800. It finds Mar in the top row of the range, then counts down three rows within that range, landing on Expenses. Change the 3 to a 4 and you get Net instead. Point the first argument at a cell holding a month name and the whole thing becomes a small interactive report.
Four arguments, and two of them cause nearly every error.
Searched only in the first row of the table array, never anywhere else. It can be a cell reference, text in quotation marks, a number, or another formula's result.
Must begin with the row containing your lookup values. Always lock it with F4 so copying the formula does not slide the range sideways, which produces wrong answers rather than obvious errors.
Counted within the range, not on the worksheet, with the header row as 1. Hard-coding this number is what makes HLOOKUP fragile, since inserting a row inside the table silently shifts the answer.
FALSE or 0 demands an exact match. TRUE or omitted returns the closest value that does not exceed the lookup value, and it requires the top row to be sorted ascending or it returns nonsense.
Replace the hard-coded number with MATCH: =HLOOKUP(B8,$B$1:$E$4,MATCH(A8,$A$1:$A$4,0),FALSE) finds the row by its label instead of by position, so inserting rows no longer breaks anything.
Three functions, one decision, and it is mostly about your data's orientation.
Labels down the first column, records in rows, returning a value from a column across. This is the layout most business data uses, which is why VLOOKUP is the one everybody learns.
Labels across the top row, records in columns, returning a value from a row below. Common in monthly budget grids, rate cards and comparison matrices.
=XLOOKUP(lookup_value, lookup_array, return_array) works in any direction because you point directly at the row or column to return. No counted index, exact match by default, and a built-in argument for what to show when nothing is found.
=INDEX(return_row, MATCH(lookup_value, header_row, 0)) does the same job on any version of Excel, is not fooled by inserted rows, and remains the standard answer in workbooks that must stay backward compatible.
If you have inherited a working workbook full of HLOOKUPs, rewriting them all introduces more risk than it removes. Learn to read them, and use XLOOKUP for anything new.
| Method | Formula | Result |
|---|---|---|
| HLOOKUP | =HLOOKUP("Mar",$B$1:$E$4,3,FALSE) | 44,800 |
| INDEX + MATCH | =INDEX($B$3:$E$3,MATCH("Mar",$B$1:$E$1,0)) | 44,800 |
| XLOOKUP | =XLOOKUP("Mar",$B$1:$E$1,$B$3:$E$3) | 44,800 |
Identical answers, three levels of durability. The HLOOKUP breaks if someone inserts a row inside the table, because the hard-coded 3 now points somewhere else. The other two reference the Expenses row directly, so they keep working no matter how the table is rearranged. That difference is the entire argument against hard-coded index numbers.
Four error types, each with a specific cause.
Either it genuinely is not in the top row, or spacing and type mismatches are hiding it. Wrap the lookup value in TRIM, and check whether a number is stored as text on one side and a real number on the other.
You asked for row 5 in a range only four rows tall. Recount from the header row within the selected range, or extend the table array to include the row you actually want.
Usually a zero, a negative number, or text where the index should be. The row index must be a positive whole number of at least 1.
The dangerous one. Caused by an unlocked table array shifting as the formula was copied, or by an approximate match on unsorted data. Press F4 on the range and set the last argument to FALSE.
Wrap the whole formula in IFNA to replace #N/A with your own message: =IFNA(HLOOKUP(...),"Not found"). Use IFNA rather than IFERROR so genuine structural errors still surface.
Where horizontal lookups genuinely earn their place.
With months across the top and line items down the side, HLOOKUP pulls any month's number into a summary cell, and pairing it with a drop-down month selector makes the summary switchable.
Tier names across the top, attributes below, one formula returning the right rate for whichever tier is selected. This is the classic horizontal layout that resists being turned vertical.
Combining HLOOKUP for the column with MATCH for the row lets you retrieve the intersection of any row and column label, which is how a rate matrix gets queried by two inputs at once.
Identical syntax and identical arguments, and Sheets has XLOOKUP as well. Formulas move between the two applications without editing.
If a horizontal layout is causing repeated trouble, Paste Special > Transpose flips it vertical once. Vertical data is easier to filter, sort, and feed into a PivotTable, which usually pays for itself quickly.
The header row of your selected range is always row 1, regardless of where it sits on the sheet. Counting from worksheet row 1 is the most common source of wrong results.
Copy an unlocked formula across and the range slides with it, so later cells search a different table. Press F4 on the range immediately after selecting it.
Leaving range_lookup blank defaults to TRUE, an approximate match that requires a sorted top row and returns quietly wrong answers when the data is not sorted. Always type FALSE.
HLOOKUP only looks in the first row of the range you give it. If your range starts a row too low, the lookup value is invisible to it and you get #N/A.
Inserting a row inside the table shifts what your index points at without producing any error. Use MATCH to find the row by its label instead.
On Excel 2021, 2024 or Microsoft 365, XLOOKUP does the same job with no counted index and exact match by default. Save HLOOKUP for reading workbooks you inherit.
A number shouldn’t need a formula to find its way to the right place.
Free 14-day trial. No credit card required.
It searches for a value in the top row of a range and returns a value from a row further down in the same column, which is the horizontal equivalent of VLOOKUP.
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The first three arguments are required, and the fourth should be set to FALSE for an exact match.
HLOOKUP searches across the top row and returns a value from a row below, while VLOOKUP searches down the first column and returns a value from a column across. The choice depends entirely on how your data is laid out.
Count rows within the range you selected, not on the worksheet. The header row you are searching is row 1, the row directly beneath it is 2, and so on.
The lookup value was not found in the top row of your range. Check for stray spaces with TRIM, confirm the range starts at the row containing your headers, and make sure numbers are not stored as text on one side.
The row index number is larger than the number of rows in your table array. Recount from the header row, or extend the range to include the row you want.
FALSE almost always. TRUE performs an approximate match that requires the top row to be sorted ascending and returns wrong answers without warning when it is not.
The table array was not locked. Select the range and press F4 to add dollar signs so it stays fixed as the formula is copied.
Yes, where it is available. XLOOKUP searches in any direction, needs no counted index number, defaults to exact match and has a built-in not-found argument. HLOOKUP is still worth knowing for older files and older Excel versions.
Replace the hard-coded row index with MATCH, so the formula finds the row by its label rather than by position within the range.
No. It only ever searches the first row of the range you give it. Adjust the table array so it begins with the row containing your lookup values.
Combine HLOOKUP with MATCH, using MATCH to find the correct row number from a label, which lets you retrieve the intersection of any row and column.
Yes, with identical syntax and arguments, and Sheets also supports XLOOKUP if you would rather use that.
Often, yes. Paste Special > Transpose flips a horizontal table vertical once, and vertical data is easier to sort, filter and summarize in a PivotTable.
Every business starts with a spreadsheet. Updoot is where you scale past it, with data already connected to where it belongs.
Start Your Free Trial →