For a simple HLOOKUP sample, with years in B1:E1 and revenue in B2:E2, use =HLOOKUP(2025,B1:E2,2,FALSE); it returns the value under 2025, which is 29000 in the example below. HLOOKUP searches across the first row of a range and returns a value from a specified row in the same column.
A working HLOOKUP sample
Suppose a worksheet has this compact report, with years arranged horizontally rather than vertically:
| B | C | D | E | |
|---|---|---|---|---|
| 1 | 2022 | 2023 | 2024 | 2025 |
| 2 | 18000 | 21500 | 24200 | 29000 |
| 3 | 240 | 275 | 301 | 348 |
Here, row 1 contains the years, row 2 contains revenue, and row 3 contains order counts. With the year values in B1:E1 and revenue in B2:E2, =HLOOKUP(2025,B1:E2,2,FALSE) returns 29000. The lookup value is 2025, the search range is B1:E2, and the number 2 tells Excel to return a value from the second row of that range.
To retrieve the order count instead, keep the same range but request its third row. With the table still in B1:E3, =HLOOKUP(2025,B1:E3,3,FALSE) returns 348. The row number is counted from the top of the selected range, not from the worksheet: row 1 of B1:E3 is worksheet row 1, row 2 is worksheet row 2, and row 3 is worksheet row 3.
What HLOOKUP does
HLOOKUP is short for horizontal lookup. It compares a lookup value with entries across the first row of a selected range. When it finds the requested entry, it returns a value from another row in the same column. The function does not search every row, and it does not return a value from a column chosen by its position; its search direction is horizontal, while its return position is specified as a row number.
The function takes four arguments: the value to find, the rectangular range to search, the row number within that range to return, and whether to use an exact or approximate match. The last argument is optional, but it is best to include it deliberately. FALSE requests an exact match; TRUE requests an approximate match. Omitting the argument makes Excel use approximate matching, which can be surprising if you expect an exact lookup.
- Lookup value: The year, label, number, or cell reference Excel should find.
- Table array: The range whose top row contains the lookup values and whose lower rows contain return values.
- Row index: A positive number identifying which row of the selected range supplies the answer.
- Match option: FALSE for an exact match or TRUE for an approximate match.
Exact matches and approximate matches
For most everyday lookups—finding a product price, a monthly total, a department name, or a particular year—use FALSE. Exact matching means Excel returns a result only when it finds the requested value in the first row. If it cannot find that value, HLOOKUP returns #N/A rather than choosing a nearby entry.
For example, imagine product names run across B1:E1 and their prices are in B2:E2: B1 is Keyboard, C1 is Mouse, D1 is Monitor, and E1 is Tablet; the prices are 45, 22, 249, and 499. With product names in B1:E1 and prices in B2:E2, =HLOOKUP("Tablet",B1:E2,2,FALSE) returns 499. If the lookup value is Tablet but the header says Tablet Stand, an exact match does not treat those labels as equivalent.
Approximate matching is useful for ordered bands such as commission tiers, grading thresholds, or shipping brackets. With approximate matching, the first row must be sorted in ascending order. Excel returns the value associated with the largest entry that is less than or equal to the lookup value. If the lookup value is below the smallest entry, the result is #N/A. An unsorted first row can produce an incorrect-looking result without an obvious warning, so do not use TRUE simply because it is the default.
For instance, a threshold row might contain 0, 100, and 500, with a discount rate below each threshold. A lookup for 320 should match the 100 threshold when the first row is ascending. For brackets where each threshold marks the start of a range, this behavior can be appropriate. For names, IDs, or product codes, exact matching is generally safer.
Another sample with labels and a different return row
HLOOKUP can return different kinds of information from a horizontal product table. Suppose B1:E1 contains Keyboard, Mouse, Monitor, and Tablet; B2:E2 contains 45, 22, 249, and 499; and B3:E3 contains stock counts of 18, 42, 7, and 12. With that table in B1:E3, =HLOOKUP("Monitor",B1:E3,3,FALSE) returns 7, the Monitor stock count. Changing the row index from 3 to 2 would return the price, 249.
A cell reference can make the lookup reusable. If G1 contains the requested product name, and product names, prices, and stock counts remain in B1:E3, =HLOOKUP(G1,B1:E3,2,FALSE) returns the price for the product named in G1. For example, if G1 contains Mouse, the result is 22. Updating G1 to Tablet changes the result to 499 without editing the formula.
Keep the lookup row and return rows inside the same selected range. If you select B1:E3, the row index 1 refers to the product labels, 2 to prices, and 3 to stock. If you instead select B2:E3, the price row becomes row index 1 and stock becomes row index 2. A common source of mistakes is counting worksheet row numbers rather than rows within the table array.
Common HLOOKUP problems and fixes
When a result is missing or unexpected, check the lookup value, range, row index, and match setting in that order. These details account for most HLOOKUP issues:
- #N/A: With FALSE, Excel may not find an exact match. Check spelling, extra spaces, and whether one value is stored as text while the other is numeric. For approximate matching, verify that the first row is sorted ascending and that the lookup value is not below its smallest entry.
- #REF!: The requested row index is greater than the number of rows in the table array. Expand the range or use a valid row number.
- #VALUE!: The row index is less than 1 or is not a usable number. Confirm that it identifies a row within the selected range.
- Wrong result with no error: Check whether the last argument was left out, causing approximate matching, and verify that the row index points to the intended return data.
- Text that appears identical does not match: Look for leading or trailing spaces, inconsistent punctuation, and data-type differences. A visible value such as 2025 might be stored as text in one place and as a number in another.
If you want a friendlier message when an exact match is missing, IFNA can handle that case. With the product table in B1:E2 and the requested product in G1, =IFNA(HLOOKUP(G1,B1:E2,2,FALSE),"Product not found") returns the price when G1 matches a product and Product not found when HLOOKUP returns #N/A. IFNA does not repair a wrong row index or every other kind of formula error, so it should not be used to hide problems that need correcting.
Exact text matching is not case-sensitive: Tablet and TABLET are treated as the same lookup text. HLOOKUP also supports wildcard characters in an exact text lookup: an asterisk can represent any sequence of characters, and a question mark can represent one character. To search for a literal asterisk or question mark, precede it with a tilde. Wildcards are useful for intentionally partial text searches, but they can match more than a reader expects, so use a complete label when possible.
HLOOKUP compared with VLOOKUP and XLOOKUP
HLOOKUP makes sense when lookup keys are arranged across the top and the results are in rows beneath them. VLOOKUP is the corresponding traditional function for keys in the first column and results to their right. If data is naturally arranged vertically, VLOOKUP is usually the more direct choice; rearranging a vertical list into a horizontal one just to use HLOOKUP is rarely helpful.
In Microsoft 365, XLOOKUP can perform horizontal lookups while specifying the lookup row and return row directly, rather than relying on a row index. With product names in B1:E1, prices in B2:E2, and the requested name in G1, =XLOOKUP(G1,B1:E1,B2:E2,"Product not found") returns the matching price or the stated message if there is no match. For G1 containing Tablet, the result is 499. XLOOKUP is available in Microsoft 365 and Excel 2021 or later, while HLOOKUP remains useful when sharing workbooks with people using older Excel versions.
Unlike HLOOKUP, XLOOKUP defaults to exact matching and allows the lookup and return ranges to be specified separately. That can make a formula easier to read and less vulnerable to a row-index mistake. However, if you are maintaining a workbook that already uses HLOOKUP, understand the existing layout before replacing formulas; a working HLOOKUP is not automatically wrong just because a newer alternative exists.
Organizing data for reliable lookups
HLOOKUP works best with a clear header row and consistent data. Put each lookup key in one cell, avoid merged cells in the lookup row, and keep each return row aligned with the matching column above it. Duplicate lookup values deserve attention: HLOOKUP returns the first matching entry it encounters from left to right, so repeated product names or years may cause it to return an earlier column than intended.
Use a range that includes the necessary lookup row and return rows, but does not accidentally start one row too low. If you copy formulas to other cells, consider whether the table reference should stay fixed. In Excel, dollar signs make a reference absolute; for example, a reference such as $B$1:$E$3 stays fixed when a formula is copied. In Microsoft 365 for Windows, you can edit a formula in the formula bar or directly in the cell; the same HLOOKUP logic applies in Excel for Mac and Excel for the web. Function argument separators can vary with regional settings, so some installations expect semicolons instead of commas even though the examples here use standard comma separators.
HLOOKUP is designed for a horizontal lookup table, not for every kind of search. If the table is likely to grow, has several repeated labels, or needs a more flexible return range, consider whether a vertical layout or XLOOKUP would be clearer. The best choice is the one that reflects how the source data is organized and that another person can audit easily.
Frequently Asked Questions
What is a simple HLOOKUP formula in Excel?
With the value to find in the first row of B1:E2 and the result in its second row, =HLOOKUP(2025,B1:E2,2,FALSE) searches for 2025 and returns the corresponding value from row 2. FALSE asks for an exact match.
What does the number 2 mean in HLOOKUP?
It means return a value from the second row of the selected table array. It does not mean worksheet row 2 unless the selected range happens to begin on worksheet row 1.
Should I use TRUE or FALSE in HLOOKUP?
Use FALSE for an exact match, which is right for most IDs, names, and product lookups. Use TRUE only when approximate matching is intended and the first row is sorted in ascending order.
Why does HLOOKUP return #N/A?
Usually, an exact lookup value is missing or differs from the value in the top row. Check for spaces, text-versus-number differences, and spelling. With approximate matching, check the sort order and whether the lookup value is at least as large as the smallest entry.
Does HLOOKUP search down a column?
No. It searches horizontally across the first row of its range, then returns a value from a row below. For a lookup key in the first column with results to its right, use a vertical lookup approach such as VLOOKUP or XLOOKUP instead.
Related
- Excel Data Model: How to Append Tables with Power Query
- Excel Data Run Analysis: A Practical Runs Test
- Excel Data Model in the Web: What Works and What Doesn’t
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Combine Cells in Excel That Have Words: Formulas and StepsTo combine words from two Excel cells, enter a formula such as =A2&" "&B2 in an empty cell;…
- Excel Formula for the Greater of Two CellsTo return the larger of two cell values in Microsoft Excel, use =MAX(A2,B2). This example assumes the two…
- Excel IF Formula Examples: Tests, Logic, and ErrorsExcel’s IF formula checks whether a condition is true or false and returns one result for each outcome.…
- Excel Text Join With a New Line: Formulas and TipsTo join text with a new line in Excel, use TEXTJOIN with CHAR(10) as the delimiter. With text…
- Excel Function to Keep a Cell Constant in Formulas ExplainedTo keep a cell reference fixed when you copy an Excel formula, make it absolute by adding dollar…
- VLOOKUP Examples for Beginners: A Practical Excel GuideIn Excel, VLOOKUP finds a value in the first column of a range and returns related information from…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.