HLOOKUP does not search multiple worksheets in a single lookup operation. To search several sheets, combine separate HLOOKUP formulas—usually with IFERROR—or use a formula that selects a sheet by name; if the data can be combined into one table, a single lookup is often simpler and more dependable.
What HLOOKUP Does
HLOOKUP searches horizontally: it looks for a value in the first row of a selected range, then returns a value from another row in the same column. The H stands for horizontal. This makes it useful when column headings run across the top and records or measurements are arranged underneath them.
For example, a worksheet might have months across row 1, revenue in row 2, and units sold in row 3. HLOOKUP can find “Mar” in the top row and return the revenue or units from the matching column. It does not automatically inspect other worksheets, and it does not search every row of its selected range.
The key distinction is between searching across columns within one worksheet and searching across worksheets. HLOOKUP handles the first. To handle the second, the formula must specify each worksheet to search, or refer to a worksheet selected by the user.
HLOOKUP Syntax and a Single-Sheet Example
The function syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The lookup value is what Excel should find in the first row of the table array. The row index is counted from the top of that array: 1 means its first row, 2 means its second, and so on. The optional final argument controls whether Excel seeks an exact or approximate match.
With the month labels Jan, Feb, Mar, and Apr in A1:D1 on a worksheet named North Region, and revenue values 1200, 1450, 1320, and 1600 in A2:D2, put Mar in B1. This formula returns 1320:
=HLOOKUP(B1,'North Region'!$A$1:$D$2,2,FALSE)
The sheet name is part of the table-array reference. Because North Region contains a space, it is enclosed in single quotation marks. The dollar signs keep the lookup range fixed if the formula is copied elsewhere. FALSE requests an exact match, which is generally the safer choice for labels such as months, product names, or department codes.
Search Several Sheets in a Set Order
When the same lookup value may appear on several worksheets, chain HLOOKUP formulas with IFERROR. Excel tries the first sheet; if that lookup returns an error, it tries the next one. This is useful for a small, known set of worksheets that share the same layout.
Suppose North Region and South Region both have month headings in A1:D1 and revenue in A2:D2. With Mar in B1, use:
=IFERROR(HLOOKUP(B1,'North Region'!$A$1:$D$2,2,FALSE),HLOOKUP(B1,'South Region'!$A$1:$D$2,2,FALSE))
If Mar is found on North Region, the result is 1320 from the example above. If it is not found there but is found on South Region, Excel returns the matching South Region value instead. If neither sheet contains the label, the formula returns an error from the final HLOOKUP.
For three or more worksheets, additional IFERROR layers can be nested. Keep the order intentional: if the same label is present on two sheets, the first successful lookup wins. A long chain quickly becomes difficult to audit, so it is best suited to a short list of worksheets rather than dozens of tabs.
- Put the most authoritative or preferred worksheet first.
- Use the same lookup range and row index on every sheet when the layouts are consistent.
- Use FALSE for exact matches unless you specifically need approximate matching and have sorted data appropriately.
- Consider what should happen when the value is absent everywhere; an explicit not-found message may be clearer than a raw error.
Choose the Worksheet from a Cell
If users need to choose which sheet to search, INDIRECT can turn text into a worksheet reference. This avoids maintaining a separate lookup formula for every possible sheet. It is useful for a workbook with a controlled set of tabs, such as monthly worksheets that all use the same cell layout.
Assume B1 contains Mar, B2 contains the exact worksheet name North Region, and each regional sheet has month headings in A1:D1 and revenue in A2:D2. This formula returns 1320:
=HLOOKUP(B1,INDIRECT("'"&B2&"'!$A$1:$D$2"),2,FALSE)
INDIRECT builds a reference from the text in B2. The single quotation marks around the sheet name allow names with spaces, and the exclamation mark separates the sheet name from the cell range. The sheet name must match the tab exactly; a typo, extra space, or renamed tab can cause a reference error.
INDIRECT has trade-offs. It is a volatile function, so Excel recalculates it frequently, and it can make a workbook harder to inspect because the reference is assembled from text. It also generally cannot resolve references to a closed external workbook. For a small workbook it may be convenient; for a large or frequently edited model, explicit references or a combined data table are usually easier to maintain.
Searching Every Sheet Is a Different Problem
A formula that checks a known set of worksheets is not the same as a formula that automatically searches every worksheet in a workbook. HLOOKUP has no built-in “all sheets” option, and ordinary formulas do not provide a simple, robust way to enumerate arbitrary tabs and search them without additional setup.
If the worksheets are monthly or regional copies of the same structure, consider appending their rows into one organized table. In Microsoft 365, Power Query can combine similarly structured tables from a workbook or folder, depending on how the data is stored. After combining, a lookup can use one source rather than a growing chain of sheet references. This also makes it easier to filter or summarize records across periods.
If the sheets have different layouts or must remain separate, use an explicit formula for each known sheet, or use a carefully maintained sheet selector. For a workbook with many worksheets, a macro or a redesigned data structure may be more appropriate, but macros require permission to run and add maintenance considerations. Do not assume a three-dimensional reference such as a range spanning several tabs can be passed to HLOOKUP as a searchable table; HLOOKUP expects a two-dimensional table array on a sheet.
Common Errors and How to Fix Them
Many multi-sheet lookup problems come from a reference or layout mismatch rather than from HLOOKUP itself. Check the following before changing the formula:
- Wrong lookup row: HLOOKUP searches only the first row of table_array. If the month names are in row 2, make row 2 the top of the selected range.
- Incorrect row index: The index is relative to the selected range, not the worksheet. For A1:D3, 2 means worksheet row 2; for A5:D7, 2 means worksheet row 6.
- Approximate match used by accident: If the last argument is omitted or TRUE, HLOOKUP can return an approximate match. Use FALSE for exact text or codes.
- Inconsistent sheet layouts: A formula that uses row index 2 on every sheet assumes the return values occupy the second row of each selected range. Check that assumption on all tabs.
- Text does not match exactly: A trailing space, different punctuation, or a number stored as text can prevent an exact match. Compare the source labels and the lookup value, and standardize them if needed.
- Sheet reference is malformed: Enclose sheet names containing spaces or punctuation in single quotation marks, then use an exclamation mark before the range.
- Unexpected blank result: A successful match can return a blank cell from the return row. IFERROR only handles errors; it does not treat a blank result as a failed lookup.
When a chained formula returns an error, test each HLOOKUP by itself first. That shows whether the value is missing from a particular sheet or whether the range, row index, or reference is wrong. Then add the IFERROR layers back one at a time. This is often faster than trying to diagnose a long nested formula as a whole.
HLOOKUP, XLOOKUP, and a Combined Table
HLOOKUP remains useful when headings run horizontally and a workbook already depends on it. However, it is limited to searching the first row of its table array, and its row index is a number that can become misleading if rows are inserted or the range changes.
XLOOKUP can search a horizontal row and return values from a corresponding horizontal row, with an optional not-found result. It does not by itself search multiple worksheets: the relevant sheet or lookup arrays still need to be specified. XLOOKUP is available in Microsoft 365 and Excel 2021 or later; older Excel versions may not recognize it.
With Mar in B1, month labels in A1:D1 on North Region, and the corresponding revenue in A2:D2, this XLOOKUP returns 1320 or the text Not found:
=XLOOKUP(B1,'North Region'!$A$1:$D$1,'North Region'!$A$2:$D$2,"Not found")
When the underlying data can be reorganized, a single table with columns such as Region, Month, and Revenue is often easier to extend than parallel worksheets. It can be filtered by both region and month, and it avoids hard-coding a new sheet reference every time a tab is added. The best choice depends on whether the sheets are a fixed reporting format or a growing data source.
Frequently Asked Questions
Can HLOOKUP search multiple sheets at once?
No. One HLOOKUP call searches the table array you provide. To check several sheets, chain separate HLOOKUP formulas, use a sheet selector such as INDIRECT, or consolidate the data.
How do I return the first match found across sheets?
Use nested IFERROR formulas in the order you want Excel to search. Excel returns the value from the first sheet where the lookup succeeds. If duplicates exist, later sheets are not checked after a successful match.
Can HLOOKUP search sheet names listed in cells?
Not directly from text alone. INDIRECT can build the worksheet reference from a cell containing the tab name, but the name must be correct and the worksheets must use a consistent layout.
Why does my formula return #N/A on one sheet?
Usually the exact lookup value is not present in the first row of that sheet’s selected range, or the lookup and source values differ in type or text. Confirm the top row, range, and spelling; also check for extra spaces.
Can I use a 3D reference to search all tabs?
No. A 3D reference can refer to corresponding cells or ranges across worksheets in functions that support that reference type, but it does not turn HLOOKUP into an all-worksheet search.
Related
- Data Cleaning Shortcuts in Excel: A Practical Guide
- Excel to PowerPoint Link: How to Keep Slides Updated
- Excel to PowerPoint Linked Cells: How to Link and Update
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel COUNTIF and SUMIF Examples That Actually WorkIn Excel, use COUNTIF(range, criteria) to count cells that meet one condition and SUMIF(range, criteria, sum_range) to add…
- Excel Test If a String Contains Text: Formula ExamplesTo test whether a cell contains a particular string in Excel, use =ISNUMBER(SEARCH("apple",A2)), replacing “apple” with the text…
- Excel INDIRECT Drop-Down Lists: Setup and FixesTo make a dependent drop-down list in Excel, create a named range for each set of choices, then…
- Excel Table Drag Formula: Lock Cell ReferencesTo keep a cell reference fixed when you fill a formula down an Excel table, add dollar signs…
- Excel Formula to Round Up to the Nearest Thousand in ExcelTo round a number in cell A2 up to the next thousand in Excel, use =ROUNDUP(A2,-3). The negative…
- Excel MATCH With Multiple Criteria: Formulas That WorkYes. In Microsoft Excel for Microsoft 365, combine the criteria into a TRUE/FALSE test, use MATCH to find…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.