HLOOKUP is used in Excel to find a value in the top row of a table and return a related value from a row below it in the same column. It is useful when information is arranged horizontally—for example, when months run across the top of a worksheet and you want to retrieve the sales figure for a chosen month.
What HLOOKUP does
The name HLOOKUP means horizontal lookup. The function scans the first row of a specified range for a value, identifies the matching column, then returns a value from another row in that column. That makes it a way to connect a label in a horizontal table to its associated information without locating the column by eye.
Imagine a small report with product names across the first row and prices beneath them. If you ask for the price of a product, HLOOKUP looks across the product-name row, finds the column containing that product, and reads the price from the chosen row underneath. It does not search every cell in the selected range: its lookup area is specifically the range’s top row.
The function is most relevant when the data is arranged with categories or lookup values across columns. If those values are listed vertically in the first column instead, VLOOKUP or another lookup function is usually a better fit.
How the function works
HLOOKUP uses four arguments: the value to find, the table range to search, the number of the row to return, and whether to use an exact or approximate match. Its syntax is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The brackets indicate that the final argument is optional; they are not typed into the formula.
- lookup_value: The item to find, such as a month, product code, or number. It can be typed into the formula or referenced from a cell.
- table_array: The range containing the lookup row and the rows from which Excel can return a result. HLOOKUP searches the range’s top row.
- row_index_num: The position of the return row within the selected range. The top row is 1, the row beneath it is 2, and so on.
- range_lookup: Use FALSE for an exact match or TRUE for an approximate match. If this argument is omitted, Excel uses TRUE.
The row number is relative to the selected range, not to the worksheet. If a table range begins in row 10, its first row still has an index of 1. HLOOKUP also returns a value from the same column where it found the lookup value; it cannot use one matching column to retrieve a value from a different column.
A practical example with months
Suppose cells B1:E1 contain Jan, Feb, Mar, and Apr; B2:E2 contain monthly unit totals of 120, 135, 150, and 142; and B3:E3 contain revenues of 1450, 1620, 1850, and 1730. Enter Mar in B5. With that layout, this formula returns the revenue from the third row of the selected range:
=HLOOKUP(B5,B1:E3,3,FALSE)
The result is 1850. Excel finds Mar in the top row, moves down to row 3 of B1:E3, and returns the value in the matching column. To return the units instead, use the second row of the range:
=HLOOKUP(B5,B1:E3,2,FALSE)
With the same cell layout, the result is 150. These examples show why the row index matters: the lookup value stays the same, but changing the index selects a different type of information associated with that month.
Exact matches and approximate matches
For labels such as department names, months, or product codes, an exact match is generally the sensible choice. Set the last argument to FALSE. If Excel cannot find that value in the first row, the function returns #N/A rather than quietly selecting a different label.
Approximate matching is useful for ordered breakpoints, such as quantity bands or grading thresholds. For example, suppose B1:E1 contain 0, 50, 100, and 200, while B2:E2 contain 0%, 5%, 10%, and 15%. With a lookup quantity of 120 in B6, use this formula:
=HLOOKUP(B6,B1:E2,2,TRUE)
The result is 10%, because 100 is the greatest threshold in the top row that is less than or equal to 120. For approximate matching, the first row must be sorted in ascending order. An unsorted row can produce an incorrect result, and a lookup value below the smallest threshold returns #N/A. For that reason, do not rely on the default matching behavior: include FALSE or TRUE explicitly so the formula’s intent is clear.
When HLOOKUP is a good choice
HLOOKUP makes sense when the lookup values genuinely run across the top of a compact table and the answer is in a row below. It can be handy in a summary sheet, a small reference table, or a report laid out with periods across columns. It also helps when a user chooses a heading from a cell and wants a formula to return the corresponding figure automatically.
It is less convenient when the table grows frequently, has many rows, or needs to return information from above the lookup row. The row index is a number, so inserting or rearranging rows can make a formula point to the wrong kind of result even if it still calculates. A well-labeled range and an explicit exact-match setting make the function easier to check and maintain.
Common HLOOKUP problems
Many HLOOKUP issues come from a mismatch between the formula and the table’s layout. Check these points when a result is unexpected:
- #N/A: With FALSE, the lookup value may not appear in the top row. Check for extra spaces, different spellings, or values stored in different formats, such as a number in one place and text in another.
- #REF!: The row index is larger than the number of rows in the selected range. Expand the range or choose a valid row number.
- Wrong result with TRUE: Approximate matching requires the first row to be sorted in ascending order. Use FALSE when you need an exact label match.
- Wrong field returned: The row index counts from the top of the selected range, not from the top of the worksheet. Recount the rows inside table_array.
- Unexpected first match: If the lookup value occurs more than once, HLOOKUP returns the first matching occurrence from the left. It does not report every duplicate.
Exact text matching with FALSE is not case-sensitive: for example, Excel treats matching upper- and lowercase letters as equivalent. With exact matching, the asterisk (*) and question mark (?) can act as wildcards in text lookups; a tilde (~) can be used to search for a literal wildcard character. These details matter if a lookup label contains those symbols.
HLOOKUP compared with VLOOKUP and XLOOKUP
HLOOKUP and VLOOKUP follow the same basic idea but search in different directions. HLOOKUP searches the first row of the selected range and returns from a row below. VLOOKUP searches the first column and returns from a column to its right. Choosing between them is largely a matter of how the source data is oriented.
XLOOKUP is a more flexible option in Microsoft 365 and Excel 2021 or later. It lets you specify the lookup row and the return row separately, so it can retrieve a result from a row above or below the lookup row. With the month example’s labels in B1:E1 and revenues in B3:E3, this formula returns 1850 for March:
=XLOOKUP(B5,B1:E1,B3:E3)
For a straightforward horizontal table, HLOOKUP remains useful and is familiar in many existing workbooks. XLOOKUP can be easier to maintain when a table changes, but it is not available in every older Excel version. If a workbook must work in older releases, check which functions its intended users have before replacing a formula.
Making HLOOKUP formulas easier to maintain
Use a clearly bounded table range and keep the lookup headings in a single, consistent top row. If the formula will be copied, consider whether the table reference should remain fixed. For example, =HLOOKUP(B5,$B$1:$E$3,3,FALSE) keeps the table range anchored as the formula is copied, while B5 can adjust to a new lookup cell. The dollar signs fix the range’s row and column references.
On Windows, F4 cycles through reference styles when a cell reference is selected during formula editing. On Mac, the equivalent shortcut is typically Command+T, though keyboard behavior can depend on the Mac’s settings and keyboard. You can also type the dollar signs directly. Keep the lookup value, table range, and return-row number understandable; splitting a complicated report into smaller, labeled tables can make formulas easier to audit.
Frequently Asked Questions
What is the main purpose of HLOOKUP in Excel?
It finds a value in the top row of a range and returns a value from a specified row below it in the same column. It is intended for tables organized horizontally.
What does the H in HLOOKUP stand for?
It stands for horizontal, describing the direction Excel searches across the first row of the selected range.
Should I use TRUE or FALSE in HLOOKUP?
Use FALSE for an exact match, which is appropriate for most text labels and codes. Use TRUE only when an approximate match is intended and the lookup row is sorted in ascending order.
Why does HLOOKUP return #N/A?
With an exact match, #N/A usually means Excel cannot find the lookup value in the range’s top row. Check the selected range, spelling, spaces, and whether the lookup value and table entry use compatible data types.
Can HLOOKUP search vertically?
No. It searches across the first row. For a table whose lookup values are in the first column, consider 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
- 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…
- Count Unique Values Across Excel Sheets with One FormulaTo count distinct nonblank values across worksheets in Excel for Microsoft 365, stack the ranges and count the…
- INDEX MATCH Examples: Exact, Two-Way, and Flexible LookupsWith product names in A2:A6, prices in B2:B6, and the product to find in E2, use =INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)). If…
- Excel Named Range Formula: Examples and SetupA named range formula example in Excel is =SUM(Sales), where the name Sales refers to a cell range…
- Excel LAMBDA in Name Manager: How to Create and Use ItYes. In Microsoft 365 Excel for Windows, you can save a LAMBDA formula as a defined name in…
- Excel Formula Line Break: How to Add One in a CellTo put a line break in an Excel formula, join the text with CHAR(10), then turn on Wrap…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.