Lookup & Reference
Cascading Lookup Across 2 Lookup Tables (XLOOKUP if_not_found & IFNA)
Search a Primary Domestic Catalog first and automatically fall back to a Secondary International/Archive Catalog when missing using XLOOKUP's 4th [if_not_found] argument.
Prefer a primary catalog, then use a secondary catalog
This example searches the same text SKU in two catalogs, unlike a chained lookup where the first table supplies a different key. When a key exists in both, the primary price wins, including a valid zero price.
Both catalogs must have complete keys and numeric nonnegative prices before lookup. Keep keys unique within each catalog: these lookups choose the first matching row, so duplicates are not resolved by price or date. Standard exact lookups are case-insensitive; this is not a case-sensitive identifier validator.
The nested fallback expresses result precedence. It does not promise that the spreadsheet engine skips evaluating the secondary expression. The price guards avoid treating a stored error or missing price as a missing SKU.
Exact-match XLOOKUP with fallback
The explicit match mode 0 treats * and ? as literal SKU characters. H2 contains this version.
=IF(OR(NOT(ISTEXT(G2)),G2=""),"Enter a text SKU",IF(OR(COUNTBLANK(A2:A4)>0,COUNTBLANK(D2:D4)>0),"Complete the catalog keys",IF(COUNT(B2:B4,E2:E4)<>6,"Enter numeric catalog prices",IF(MIN(B2:B4,E2:E4)<0,"Prices must be nonnegative",XLOOKUP(G2,A2:A4,B2:B4,XLOOKUP(G2,D2:D4,E2:E4,"Unlisted SKU",0),0)))))VLOOKUP fallback with literal wildcard characters
The VLOOKUP version escapes ~, * and ? in that order so a literal SKU cannot accidentally match a different code. IFNA handles absent keys; other failures are not intentionally replaced with a missing-key result. H3 contains this version.
=IF(OR(NOT(ISTEXT(G2)),G2=""),"Enter a text SKU",IF(OR(COUNTBLANK(A2:A4)>0,COUNTBLANK(D2:D4)>0),"Complete the catalog keys",IF(COUNT(B2:B4,E2:E4)<>6,"Enter numeric catalog prices",IF(MIN(B2:B4,E2:E4)<0,"Prices must be nonnegative",IFNA(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"),A2:B4,2,FALSE),IFNA(VLOOKUP(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(G2,"~","~~"),"*","~*"),"?","~?"),D2:E4,2,FALSE),"Unlisted SKU"))))))Try the example
Use the original values or change them to test your understanding.