When records are split across sheets — one tab per region or month — chain lookups with IFERROR: try the first sheet, and if the key isn’t there, fall through to the next.
The example
An ID found on the second sheet.
| A | B | |
|---|---|---|
| 1 | Lookup | Found on |
| 2 | ID 240 | Sheet2 → $3,100 |
The formula
Chain lookups with IFERROR:
How it works
IFERROR turns “not found” into “try the next”:
- Each
VLOOKUP(…, SheetN!table, …)searches one sheet. - Wrap it in
IFERRORwhose fallback is the next sheet’s lookup. - Nest them: a miss on Sheet1 tries Sheet2, a miss there tries Sheet3, and so on.
- The innermost lookup has no IFERROR — or wrap the whole thing in one to show a final “not found.”
Better: consolidate first. Chained lookups get unwieldy past a few sheets. In 365, VSTACK(Sheet1!t, Sheet2!t, Sheet3!t) stacks the tables so one XLOOKUP searches them all. Better still, keep the data in one table with a “sheet/region” column — splitting records across sheets is what makes this hard.
Try it: interactive demo
Look up an ID across 3 sheets.
Variations
Stack then lookup (365)
One lookup over all sheets:
Which sheet?
Return the source:
Final not-found
Wrap the whole chain:
Pitfalls & errors
Order matters with duplicates. If a key exists on two sheets, the chain returns the first sheet’s value.
Gets long fast. Beyond 3–4 sheets, switch to VSTACK or consolidate the data — deep IFERROR nesting is hard to maintain.
Exact match (0/FALSE). Use exact match in each VLOOKUP so a miss truly errors and falls through.
Practice workbook
Frequently asked questions
How do I look up a value across multiple sheets in Excel?
Is there a cleaner way in Excel 365?
What if the key is on two sheets?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class