Lookup & Reference
XLOOKUP across multiple tables or sheets: chained fallback and VSTACK
Search active and archived lookup tables in priority order by nesting XLOOKUP inside if_not_found or stacking ranges with VSTACK.
Two clean ways to search multiple tables or worksheets
Operational workbooks frequently split active SKUs and discontinued SKUs onto separate tables or tabs (such as Active!A2:B50 and Archive!A2:B50). Instead of wrapping multiple VLOOKUP calls in IFERROR, XLOOKUP's fourth argument (if_not_found) accepts a second XLOOKUP directly.
Alternatively, in modern dynamic-array spreadsheets, VSTACK(table1_keys, table2_keys) stacks the key columns and return columns vertically inside a single XLOOKUP.
| Active SKU (A) | Active Price (B) | Archive SKU (D) | Archive Price (E) |
|---|---|---|---|
| PT-101 | 48 | PT-088 | 32 |
| PT-102 | 75 | PT-094 | 64 |
| PT-103 | 120 | PT-101 | 41 (old archived price) |
Chained XLOOKUP preserves table priority
When G2 is "PT-094", the outer XLOOKUP misses A2:A4 and immediately runs the inner XLOOKUP on D2:D4, returning 64. When G2 is "PT-101"—which exists in both tables—the outer XLOOKUP finds 48 in Active rather than returning the outdated 41 in Archive.
Across separate worksheets in the editor, replace D2:D4 and E2:E4 with Archive!A2:A4 and Archive!B2:B4.
=XLOOKUP(G2, A2:A4, B2:B4, XLOOKUP(G2, D2:D4, E2:E4, "Not listed"))Single-formula alternative with VSTACK
When you stack three or more regional sheets, VSTACK keeps the formula compact. Put the highest-priority range first inside both VSTACK calls so first-match search order matches your chained lookup.
=XLOOKUP(G2, VSTACK(A2:A4, D2:D4), VSTACK(B2:B4, E2:E4), "Not listed")Try the example
Use the original values or change them to test your understanding.