On this page · 11 sections
- HLOOKUP syntax
- Which Excel versions this covers
- Sample data: quarterly sales by region
- How to use HLOOKUP, step by step (exact match)
- HLOOKUP example with approximate match: discount bands
- HLOOKUP errors and fixes
- HLOOKUP vs VLOOKUP vs XLOOKUP
- When XLOOKUP is better than HLOOKUP
- For IT teams and businesses
- How eCorpIT can help
- References
Summary. To use HLOOKUP in Excel, type =HLOOKUP(lookup_value, table_array, row_index_num, FALSE). HLOOKUP searches the top row of a range for your value and returns the entry in the same column, a set number of rows down. In the sample table below, =HLOOKUP("Q3",$A$1:$E$4,3,FALSE) finds Q3 in row 1 and returns 40,100 from the third row. FALSE asks for an exact match; TRUE or a blank gives an approximate match, which needs the top row sorted in ascending order. A row number below 1 returns #VALUE!, one larger than the range returns #REF!, and a missing exact match returns #N/A. HLOOKUP is listed for Excel for Microsoft 365 on Windows and Mac and for Excel 2024, 2021, 2019 and 2016, with no restriction for Excel for the web. Microsoft now recommends XLOOKUP, which needs no row number and matches exactly by default, but XLOOKUP is not available in Excel 2016 or 2019.
About this article. For anyone whose headings run across the top of a sheet, such as budgets, forecasts and rate cards. We checked syntax, examples, errors, shortcuts and versions against Microsoft Support and Microsoft Learn on 15 November 2026, and recomputed every result. We are not affiliated with Microsoft. Researched with AI assistance and reviewed by our editorial team.
HLOOKUP syntax
Microsoft gives the syntax as HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
lookup_value(required): the value to find in the first row; a value, a cell reference or a text string.
table_array(required): the range or range name to search. HLOOKUP compares against its first row.
row_index_num(required): the row to return, where 1 is the top row of the range.
range_lookup(optional): FALSE for an exact match; TRUE or blank for an approximate match.
Uppercase and lowercase text are treated as the same.
Which Excel versions this covers
The steps work in Excel for Windows, Mac and the web. The newest releases on 15 November 2026 are Microsoft 365 Apps Current Channel Version 2610 (Build 20527.20196) and Office for Mac 16.115, both from 10 November 2026. Excel 2016 and 2019 reached end of support on 14 October 2025 and Excel 2021 retired on 13 October 2026; HLOOKUP still works there, without updates.
Sample data: quarterly sales by region
Type this into a blank sheet starting at cell A1:
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 1 | Region | Q1 | Q2 | Q3 | Q4 |
| 2 | North | 42,000 | 45,500 | 47,250 | 51,000 |
| 3 | South | 38,400 | 36,900 | 40,100 | 43,800 |
| 4 | West | 29,750 | 31,200 | 33,600 | 35,900 |
How to use HLOOKUP, step by step (exact match)
- Click an empty cell, for example G1.
- Type
=HLOOKUP(. After the opening bracket, Excel shows the syntax as hover text.
- Type "Q3" with the double quotes, or click a cell that contains Q3, then a comma. Without quotes, Excel reads Q3 as a reference to cell Q3 (column Q, row 3) and looks up whatever that cell holds.
- Select A1:E4 and press F4 to make it absolute (
$A$1:$E$4) so it does not shift when copied. On a Mac, press Cmd+T or F4.
- Type
,3,FALSE)and press Enter.
=HLOOKUP("Q3",$A$1:$E$4,3,FALSE)
The result is 40,100: Q3 is in column D, and row 3 of the range is South. Change the 3 to 2 for North (47,250) or 4 for West (33,600).
Let Excel find the row number. Nest MATCH inside HLOOKUP so the region name picks the row:
=HLOOKUP("Q3",$A$1:$E$4,MATCH("South",$A$1:$A$4,0),FALSE)
MATCH with a match type of 0 returns the position of the exact match: 3, because South is the third cell in A1:A4. The formula again returns 40,100, and works in every version listed above.
From another sheet. Put the sheet name and an exclamation mark before the range, with apostrophes around a name that contains spaces or numbers. The rules match VLOOKUP between two sheets.
=HLOOKUP("Q3",'Sales 2026'!$A$1:$E$4,3,FALSE)
HLOOKUP example with approximate match: discount bands
Approximate match suits a top row of thresholds rather than exact keys. Add this below the sales table:
| Row | A | B | C | D | E |
|---|---|---|---|---|---|
| 7 | Order value from | 0 | 100 | 500 | 1,000 |
| 8 | Discount | 0% | 5% | 10% | 15% |
=HLOOKUP(640,$B$7:$E$8,2,TRUE)
This returns 10%. HLOOKUP cannot find 640, so it uses the largest value in row 7 that is less than 640 (500) and returns the discount beneath it. An order of 1,000 returns 15%; an order of 80 returns 0%. Two rules apply:
- Sort the top row in ascending order, left to right. Otherwise HLOOKUP may return the wrong band without any error.
- Start the bands at the lowest possible value (0 here). A lookup value below the first threshold, such as -5, returns #N/A.
HLOOKUP errors and fixes
The causes below come from Microsoft's HLOOKUP, #N/A and #NAME? help pages.
| What you see | Cause | Fix |
|---|---|---|
| #N/A | No exact match for the lookup value in the top row (FALSE) | Check spelling and stray spaces in the heading; TRIM removes leading and trailing spaces |
| #N/A | Lookup value smaller than the first value in a sorted top row (TRUE) | Add a starting band such as 0 |
| Wrong value, no error | TRUE or blank with an unsorted top row | Sort the top row ascending, or use FALSE |
| #REF! | Row number larger than the number of rows in the range | Extend the range or lower the row number |
| #VALUE! | Row number less than 1 | Use 1 or higher |
| #NAME? | Text such as a region name typed without double quotes, or a misspelt function name | Add the quotes or correct the spelling |
For genuine misses, wrap the formula in IFNA, which returns a value you choose instead of #N/A. With exact match on text, a question mark matches any single character, an asterisk any sequence, and a tilde before either finds the literal character.
HLOOKUP vs VLOOKUP vs XLOOKUP
This comparison draws on Microsoft's three function pages and its function list, which marks XLOOKUP as new in Excel 2021.
| HLOOKUP | VLOOKUP | XLOOKUP | |
|---|---|---|---|
| Searches | Top row of the range | Left-most column of the range | Any single row or column you choose |
| Result position | Row number | Column number | A separate return range |
| Default match | Approximate (TRUE) | Approximate (TRUE) | Exact |
| Can return data above or to the left | No | No | Yes |
| Built-in not-found result | No (wrap in IFNA) | No (wrap in IFNA) | Yes, the if_not_found argument |
| Available in | Microsoft 365, 2024, 2021, 2019, 2016, web | Microsoft 365, 2024, 2021, 2019, 2016, web | Microsoft 365, 2024, 2021, web (not 2016 or 2019) |
Microsoft's lookup overview says that once you are comfortable with VLOOKUP, HLOOKUP takes the same arguments but searches rows instead of columns.
When XLOOKUP is better than HLOOKUP
Microsoft's HLOOKUP page opens with a tip recommending XLOOKUP, and the XLOOKUP page says it can replace HLOOKUP. The horizontal version of the sample lookup points at the header row and result row directly, with no row number, and returns 40,100:
=XLOOKUP("Q3",B1:E1,B3:E3)
XLOOKUP is the better choice when:
- Rows get inserted. Microsoft's XLOOKUP announcement names the fixed column number as a VLOOKUP weakness: insert or delete a column and you must edit the number by hand. HLOOKUP's row number has the same problem with rows; Microsoft says XLOOKUP has none of those limitations.
- You need a two-way lookup. Nest one XLOOKUP inside another, as Microsoft's own example does. The first formula below returns 40,100.
- You want a clean not-found result. The fourth argument, if_not_found, replaces IFNA.
- The thresholds are not sorted. Match mode -1 returns an exact match or the next smaller item, and Microsoft's page mentions sorted data only for the binary search modes (2 and -2). The second formula returns 10%.
=XLOOKUP("South",A2:A4,XLOOKUP("Q3",B1:E1,B2:E4))
=XLOOKUP(640,$B$7:$E$7,$B$8:$E$8,,-1)
Stick with HLOOKUP, or INDEX with MATCH, when the workbook must open in Excel 2016 or 2019, where XLOOKUP is not available. This INDEX and MATCH two-way lookup works in every version listed here and also returns 40,100:
=INDEX($B$2:$E$4,MATCH("South",$A$2:$A$4,0),MATCH("Q3",$B$1:$E$1,0))
Microsoft has said HLOOKUP will stay supported. Our XLOOKUP guide covers match modes, search modes and multiple criteria.
For IT teams and businesses
Budgets, forecasts and rate cards often run months or price bands across the top, and that is where HLOOKUP breaks most: someone inserts a row for a new cost line and every hard-coded row number below it now points at the wrong line. In shared templates, use MATCH for row numbers, or XLOOKUP once every user has Microsoft 365, Excel 2021 or later, or the web version. Keep approximate-match rate tables sorted and lock their cells, and record which Excel versions each template must support.
How eCorpIT can help
eCorpIT is a Gurugram-based technology organisation, founded in 2021, assessed at CMMI Level 5 and MSME certified, with senior-led engineering teams working across AWS, Microsoft and Google platforms. Our data and analytics services team turns spreadsheet budgets and rate cards into governed reports that do not depend on hand-counted row numbers. Talk to us at /contact-us/.
Last updated: 15 November 2026.
