HLOOKUP in Excel searches across the first row of a range for a value, then returns a value from another row in the same column. For example, with product codes in the top row and prices beneath them, HLOOKUP can find a code and return its price; use FALSE for an exact match in most everyday lookups.
What HLOOKUP means
HLOOKUP stands for “horizontal lookup.” It is designed for data arranged across columns: the values to search are in the top row, and the information to retrieve is in a row below. Once Excel finds the lookup value in that first row, it moves down within the matching column to the row you specify.
This layout is the key distinction between HLOOKUP and VLOOKUP. VLOOKUP searches down the first column of a range and returns a value farther to the right. HLOOKUP searches across the first row and returns a value farther down. Neither function searches in every direction: HLOOKUP cannot find a value in a lower row and return something above it.
HLOOKUP remains useful when a worksheet is already organized with categories or dates across its top and corresponding data underneath. It is also encountered in older workbooks and formulas that need to remain compatible with earlier Excel versions. If you are designing a new worksheet, it is often worth considering whether a vertical table would be easier to maintain.
How the HLOOKUP arguments work
HLOOKUP takes four arguments. The first is the value to search for. The second is the entire range containing both the search row and the result rows. The third is a row number counted from the top of that selected range—not the worksheet’s actual row number. The fourth determines whether Excel looks for an exact or approximate match.
- Lookup value: The value, text, or cell reference Excel should find in the range’s top row.
- Table range: The rectangular range that includes the lookup row and the rows from which results may be returned.
- Row number: The position of the result row inside that range. The first row is 1, the next is 2, and so on.
- Match type: Use
FALSEfor an exact match orTRUEfor an approximate match. If this argument is omitted, Excel uses approximate matching.
The row number must be at least 1 and no greater than the number of rows in the selected range. A value of 1 returns the lookup row itself, which is rarely the intended result but can be valid.
Build an exact-match HLOOKUP
Suppose A1:D1 contains product codes P-101, P-204, P-318, and P-422; A2:D2 contains their unit prices 12.50, 19.95, 8.75, and 15.00; and A3:D3 contains stock counts 24, 7, 18, and 11. With that layout, the formula below searches for P-204, selects row 2 of the range, and returns the corresponding price:
=HLOOKUP("P-204",A1:D3,2,FALSE)
The result is 19.95. The range’s first row contains the product codes, and row 2 of the range is the price row. To return the stock count for the same product, use this formula with the same A1:D3 layout:
=HLOOKUP("P-204",A1:D3,3,FALSE)
It returns 7, because the stock counts are in the third row of the selected range. In a working sheet, the lookup value will often be a cell reference rather than text typed into the formula. If E2 contains P-204 and the product data remains in A1:D3, enter:
=HLOOKUP(E2,A1:D3,2,FALSE)
This makes the lookup easier to reuse: change E2 to another product code and the formula returns that product’s price. The value in E2 must match a value in the range’s top row for an exact match to succeed.
Exact match or approximate match?
For identifiers such as product codes, employee names, invoice numbers, or month labels, use FALSE. Exact matching tells Excel to find the same value rather than a nearby one. If Excel cannot find an exact match, HLOOKUP returns #N/A.
An approximate match is appropriate when the first row contains ordered breakpoints, such as score thresholds or tax-band boundaries, and you want the result for the greatest threshold that does not exceed the lookup value. For approximate matching, the first row must be sorted in ascending order. For example, with 0, 60, 70, and 80 in A1:D1 and the labels F, D, C, and B in A2:D2, this formula returns C for a score of 76:
=HLOOKUP(76,A1:D2,2,TRUE)
Excel finds the largest threshold that is no greater than 76: 70. The corresponding label is C. If the thresholds are not sorted ascending, an approximate lookup can return an unexpected answer. A score below the smallest threshold also has no suitable lower breakpoint. When the lookup values are identifiers rather than ordered thresholds, prefer FALSE; it avoids relying on sort order.
Arrange the data for reliable results
HLOOKUP’s required layout can be awkward. Every value Excel might search for must be in the top row of the selected range, and every return value must be in a row beneath it, in the same column. If product codes are listed down column A instead, HLOOKUP is not the natural choice. Reorganizing a table into a conventional format—with one record per row and field names across the top—usually makes sorting, filtering, and adding records simpler.
Keep lookup values consistent. A number stored as text may not match a numeric value as expected, and a text entry with an extra space can fail an exact lookup even if it looks identical on screen. Check the underlying values and cell formats when a seemingly correct match returns #N/A. Also check whether the lookup row contains duplicates: HLOOKUP returns the first matching entry it encounters as it searches from left to right, so it cannot distinguish between duplicate keys.
Use a range that includes all relevant result rows, but avoid accidentally selecting a range that begins one row too high or too low. Because the row number is relative to the selected range, changing the range’s starting row can change what a row number means.
Common HLOOKUP errors and how to fix them
#N/A: With an exact match, Excel could not find the lookup value in the first row. Check spelling, spaces, data type, and whether the value is actually inside the selected range. With approximate matching, confirm that the top row is sorted and includes a suitable threshold.#REF!: The requested row number is greater than the number of rows in the selected range. Expand the range or correct the row number.#VALUE!: The row number is less than 1 or is not a usable number. Check the third argument.- A plausible but incorrect result: Check whether the fourth argument was omitted. Because the default is approximate matching, an omitted argument can produce an unexpected result, particularly with unsorted lookup values. Specify
FALSEwhen an exact match is required.
When debugging, inspect the top row of the range first, then confirm the row number by counting from the range’s first row. It can help to select the referenced range in the formula bar and verify that it covers the intended lookup and return cells. If the formula is copied to other cells, confirm that its range references still point to the intended data.
HLOOKUP compared with VLOOKUP and XLOOKUP
Choose HLOOKUP when the lookup keys are in a top row and the answers are below them. Choose VLOOKUP when keys are in the first column and answers are to their right. These functions have a similar structure, but their search direction and return direction differ. Both can be inconvenient if the worksheet layout changes, because their return position is specified as a number within the selected range.
XLOOKUP can search horizontally or vertically and lets you specify the lookup range and return range separately. It uses exact matching by default, and it does not require a numbered row argument. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. With the product codes in A1:D1 and prices in A2:D2, this formula returns 19.95 for P-204:
=XLOOKUP("P-204",A1:D1,A2:D2,"Not found")
The optional “Not found” text gives a clearer alternative to an error when the code is absent. If a workbook must work in an Excel version without XLOOKUP, HLOOKUP may be a more compatible choice. The best option depends on the workbook’s supported Excel versions and the shape of its data.
Other ways to do a horizontal lookup
INDEX and MATCH can also perform a horizontal lookup. MATCH finds the position of a value within a row, and INDEX returns a value at that position in another row. Unlike HLOOKUP, this approach separates the search row from the return row, which can be useful when they are not part of one simple rectangular range. With codes in A1:D1 and prices in A2:D2, this formula returns 19.95 for P-204:
=INDEX(A2:D2,1,MATCH("P-204",A1:D1,0))
The final argument of MATCH is 0, which requests an exact match. For a one-row return range, INDEX’s row argument is 1, while MATCH supplies the column position. This formula is more involved than a basic HLOOKUP, but it is a useful option in older Excel versions where XLOOKUP is unavailable and the lookup and return ranges need to be specified independently.
Frequently Asked Questions
What does HLOOKUP do in Excel?
It searches for a value in the first row of a selected range and returns a value from a specified row in the same column. It is meant for data arranged horizontally.
What does FALSE mean in HLOOKUP?FALSE requests an exact match. If Excel cannot find the lookup value in the first row, the formula returns #N/A. It is usually the right setting for codes and labels.
What happens if I leave out the last HLOOKUP argument?
Excel uses approximate matching. That can be useful for sorted thresholds, but it can return an unintended result if you expected an exact match or if the lookup row is not sorted ascending.
Can HLOOKUP search for a value in any row?
No. It searches only the first row of the range you provide. To search a different row, make that row the first row of the selected range, or use a different lookup method such as XLOOKUP.
Why does HLOOKUP return #N/A when the value looks present?
Check for extra spaces, text-versus-number differences, a misspelled value, or a range that does not include the cell. Also verify that the lookup value is in the range’s first row and that the formula uses the intended match type.
Related
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
- Excel Data Analysis Skills: What They Include and Why
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Find and Replace Paragraph Mark: The Right MethodIn Excel for Microsoft 365 on Windows, press Ctrl+H, click the Find what box, and press Ctrl+J to…
- XLOOKUP Excel Conditional Search: Formulas and ExamplesTo use XLOOKUP for a conditional search in Excel, put the condition in its lookup array; for multiple…
- Excel IF Formula Examples for Everyday Spreadsheet TasksExcel’s IF formula checks whether a condition is true and returns one result if it is, and another…
- Excel Time Subtraction: Formulas and Overnight TimesTo subtract one time from another in Excel, subtract the start time from the end time: with a…
- Excel Table Sort by Two Columns: A Step-by-Step GuideTo sort an Excel table by two columns in Microsoft 365 for Windows, click a cell in the…
- Excel MID Cell Reference: Extract Text from a CellTo extract characters from a cell in Excel, use the MID function with that cell as its first…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.