For an advanced Excel HLOOKUP, use =HLOOKUP(F1,$A$1:$D$4,2,FALSE) when the value to find is in F1, the lookup keys are across the first row of A1:D4, and the result is in that table’s second row. The final FALSE requests an exact match; advanced uses build on this pattern with approximate matches, dynamic row selection, wildcards, and error handling.
What HLOOKUP Does
HLOOKUP searches horizontally: it looks for a value in the first row of a rectangular table and returns a value from another row in the same column. The H stands for horizontal. It is useful when a worksheet is organized with categories, dates, product codes, or other lookup keys across columns, and related data underneath them.
For example, a quarterly report might put region names across row 1, sales figures across row 2, and unit counts across row 3. HLOOKUP can find a region in the top row and return its sales or unit count. It does not search every row in the selected range: only the range’s first row is searched. Nor does it search left or right from a match to return a value above the lookup row.
HLOOKUP remains available in Microsoft 365 for Windows, but newer lookup functions are often easier to maintain in newly designed workbooks. Understanding HLOOKUP is still useful when reading older spreadsheets or working with data laid out horizontally.
Syntax and the Four Arguments
Assuming the lookup value is in F1 and the horizontal lookup table is A1:D4, the general HLOOKUP syntax is =HLOOKUP(F1,A1:D4,2,FALSE). Its arguments, in order, are:
- lookup_value: The value to find in the first row of the table, such as a region name or date.
- table_array: The full range containing both the lookup row and the rows that may supply the result. Lock this range with dollar signs when copying a formula, as in
$A$1:$D$4. - row_index_num: The position of the result row within the selected table. The first row is 1, the second is 2, and so on. This is a position, not a worksheet row number.
- range_lookup: Use FALSE for an exact match or TRUE for an approximate match. If this argument is omitted, Excel uses TRUE, which can produce an unexpected result when the first row is not sorted.
For instance, if the table begins on worksheet row 10, its second row is still row_index_num 2. A row index less than 1, or larger than the number of rows in the table, causes an error. The lookup row must be the top row of the table_array.
Exact Match with a Fixed Lookup Table
Exact matching is the safest default for names, codes, and identifiers. Assume A1:D3 contains North, South, East, and West in row 1; sales of 120, 150, 95, and 130 in row 2; and costs in row 3. If F1 contains South, this formula returns 150:
With the region in F1 and the lookup table in A1:D3, use row 2 for sales:
=HLOOKUP(F1,$A$1:$D$3,2,FALSE)
The dollar signs keep the lookup range fixed if the formula is filled down or copied elsewhere. The reference F1 is relative, so it changes to F2 when copied down. If the lookup value should stay in F1 too, write it as $F$1. Choose references based on how the formula will be copied, rather than adding dollar signs automatically.
Exact match does not mean case-sensitive match: HLOOKUP treats uppercase and lowercase letters as equivalent. It does require the lookup value to correspond to a value in the top row; differences such as extra spaces or a number stored as text can still prevent a match.
Selecting the Result Row Dynamically
A fixed row index is simple, but a workbook with many measures may need to return whichever measure a user selects. One approach is to keep measure names in a separate vertical list aligned with the data rows. Assume B1:E1 contains North, South, East, and West; B2:E4 contains the corresponding Sales, Units, and Returns data; and A2:A4 contains those three measure names. If G1 contains South and G2 contains Units, the formula below returns the Units value at the intersection.
With region in G1, measure name in G2, headers in B1:E1, data in B2:E4, and measure labels in A2:A4:
=HLOOKUP(G1,$B$1:$E$4,MATCH(G2,$A$2:$A$4,0)+1,FALSE)
MATCH finds the selected measure’s position in A2:A4. The addition of 1 converts that position to the row index within B1:E4, whose header row occupies position 1. For example, Units is the second item in A2:A4, so the resulting HLOOKUP row index is 3. The formula then finds South across the first row and returns the value from the third row of the table. The labels and data rows must remain in the same order for this arrangement to work.
Approximate Matches for Thresholds
Approximate matching is useful for thresholds, such as grading bands, shipping tiers, or commission brackets. It is not a substitute for exact matching when looking up unique codes. Assume A1:E1 contains the ascending lower limits 0, 50, 100, 250, and 500, while A2:E2 contains labels Free, Basic, Standard, Premium, and Priority. With a quantity of 180 in G1, this formula returns Standard:
With ascending thresholds in A1:E1, their labels in A2:E2, and the quantity in G1:
=HLOOKUP(G1,$A$1:$E$2,2,TRUE)
For an approximate match, Excel uses the largest value in the first row that is less than or equal to the lookup value. Since 100 is the largest listed threshold not exceeding 180, the result is the label beneath 100. The first row must be sorted in ascending order. If it is not, the result may be incorrect rather than an obvious error. A lookup value below the smallest threshold also has no lower matching threshold and can return an error.
Threshold tables should define their boundaries deliberately. A table containing lower limits makes the rule clear: 100 begins Standard, and 250 begins Premium. Test values at each boundary and just below or above it, especially when the result affects operational decisions.
Wildcards and Text Matching
When range_lookup is FALSE, HLOOKUP can use wildcards in a text lookup value. An asterisk represents any number of characters, a question mark represents one character, and a tilde escapes a wildcard when you need to search for the literal character. This can help when headers contain a consistent prefix but vary in their endings.
Assume A1:D1 contains East Region, West Region, East Online, and West Online, and A2:D2 contains their sales. With the text East* in F1, the following exact-mode formula returns the value under the first header that matches the pattern, East Region:
With wildcard text in F1 and lookup headers in A1:D1, with values in A2:D2:
=HLOOKUP(F1,$A$1:$D$2,2,FALSE)
Because more than one header can match East*, the formula returns the first match from left to right. Do not use a broad wildcard when the intended match is ambiguous. HLOOKUP is also not case-sensitive, so changing the capitalization of a search term will not make the match case-sensitive.
Handling Missing Values and Other Errors
An exact lookup with no matching header commonly returns #N/A. That may indicate a genuine missing item, but it can also point to an extra space, a spelling difference, or a mismatch between text and numeric values. Before hiding the error, check the source data and confirm that the lookup range starts with the correct header row.
If a missing header is an expected possibility, IFNA can replace only the #N/A result with a useful message. Assume F1 contains a region and A1:D3 is the same region-and-sales table described earlier:
With the lookup value in F1 and the table in A1:D3:
=IFNA(HLOOKUP(F1,$A$1:$D$3,2,FALSE),"Region not found")
For an unlisted region, the result is Region not found; for a listed region, the formula returns its sales number. IFNA is more targeted than IFERROR, which can conceal other problems too, including an invalid row index. Use a fallback message only when it helps the spreadsheet user understand what happened. If the formula returns #REF!, check whether the row index exceeds the height of the table; if it returns #VALUE!, inspect the arguments and their types.
HLOOKUP Compared with Alternatives
The best lookup function depends on the layout and the Excel version in which the workbook must run. HLOOKUP is straightforward when the lookup keys are already across the top and the required result is below them. Its main limitation is that it can return only from a row beneath the lookup row in the selected table.
- XLOOKUP: In Microsoft 365 and Excel 2021 or later, XLOOKUP can search one row and return from another row, with a separate not-found argument. It is more flexible, but a workbook shared with users on older Excel versions may not support it.
- INDEX and MATCH: This combination works in older Excel versions and can separate the lookup range from the return range. It is useful when you need more control over the ranges, but its formula is less compact.
- Rearranging the data: If you can organize records with field names in columns and each record on a row, a vertical lookup or a table-based approach is often easier to filter, extend, and analyze. HLOOKUP is not inherently wrong; the layout should suit the task and the workbook’s future use.
XLOOKUP, XMATCH, FILTER, SORT, UNIQUE, SEQUENCE, and LET are available in Microsoft 365 or Excel 2021 and later. Do not choose a newer function without checking compatibility if the workbook will be opened in older Excel releases. HLOOKUP is supported in current Excel, which makes it a practical option for compatible legacy files.
Practical Checks Before Sharing a Formula
A dependable HLOOKUP formula depends as much on the table as on the function. Check the structure and test the results before copying a formula across a report or distributing a workbook.
- Confirm that the lookup keys are in the first row of the selected table_array, not merely somewhere within it.
- Use FALSE for exact lookup tasks, and use TRUE only when the first row is intentionally sorted for approximate matching.
- Count the rows in the table_array and verify that row_index_num points to the intended result row.
- Use absolute references for ranges that must stay fixed when you copy the formula. Leave lookup references relative only when they should move.
- Test a known match, an unknown value, and any threshold boundary. Check whether text and numbers are stored consistently.
For a report that changes shape frequently, a fixed range such as A1:D3 may exclude newly added columns. Consider expanding the range when data grows, or using a structured data design that makes the lookup area easier to maintain. A formula can be syntactically correct yet return stale results if its table range does not include new data.
Frequently Asked Questions
What does the 2 mean in HLOOKUP?
The 2 is row_index_num: the second row within table_array. It is not the worksheet’s row 2. If the selected table starts on worksheet row 10, row index 2 refers to worksheet row 11.
Should I use TRUE or FALSE in HLOOKUP?
Use FALSE for an exact match, which is the right choice for most names, dates, and identifiers. Use TRUE for approximate matching only when the first row is sorted in ascending order and the lookup is intended to select the largest threshold not above the value.
Why does HLOOKUP return #N/A?
With exact matching, #N/A usually means Excel could not find the lookup value in the first row of the selected range. Check spelling, spaces, data types, and whether the range begins on the correct row. With approximate matching, check the table’s sorted order and whether the value is below the smallest threshold.
Can HLOOKUP return a value from a row above the match?
No. HLOOKUP searches the first row of its table_array and returns from a row below it. If the value you need is above the lookup row, choose a different function or reorganize the table.
Is HLOOKUP case-sensitive?
No. HLOOKUP does not distinguish uppercase from lowercase. If case-sensitive matching is a requirement, use a different method designed for case-sensitive comparison rather than relying on HLOOKUP.
Can I use HLOOKUP for multiple criteria?
Not directly as a built-in multi-criteria lookup. One option is to create a helper row that combines the criteria into a unique key, then look up that key. For more flexible multi-condition work, consider a suitable INDEX/MATCH or newer lookup approach and verify it works in the Excel versions used by the workbook’s audience.
Related
- Excel Data Model in the Web: What Works and What Doesn’t
- Excel Data Analysis Toolkit: Setup, Tools, and Practical Uses
- Excel Data Analysis Sampling: Methods, Steps, and Uses
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- How to Copy Formula Data in Excel Without Breaking ReferencesTo copy formula data in Excel, select the cells, press Ctrl+C, choose the destination, and use Home >…
- If Excel Contains Specific Text: Formulas That WorkTo check whether a cell contains specific text in Excel, use IF with SEARCH and ISNUMBER. With the…
- Excel Attendance Sheet With Time: Setup and FormulasTo make an Excel attendance sheet with time, create columns for the date, employee, scheduled start, clock-in, clock-out,…
- How to Sort Excel Alphabetically by First ColumnTo sort an Excel list alphabetically by its first column, click a cell in that column and choose…
- 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 IFERROR vs ISERROR: Which Should You Use?Use IFERROR when you want to replace an error with a value such as a message or blank-looking…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.