Lookup & Reference
Merge two tables by ID: Left Join, Inner Join, and Orphan Key audit
Join an Orders fact table to a Customers master table using XLOOKUP enrichment columns and FILTER for Inner Join vs unmatched Anti-Join audits.
SQL-style Left Join, Inner Join, and Anti-Join right in spreadsheet cells
Add customer-tier enrichment with XLOOKUP and a separate boolean match flag in column H. This keeps a legitimate tier called UNMATCHED distinct from a missing key.
FILTER(A2:D5,H2:H5,"None") returns matched rows; FILTER with H=FALSE returns unmatched rows.
| OrderID (A) | CustID (B) | Amount (C) | Joined Tier (D) | Join Status |
|---|---|---|---|---|
| ORD-101 | C-10 | $400 | Enterprise | Matched (Inner & Left) |
| ORD-102 | C-99 | $150 | UNMATCHED | Orphan! (Left / Anti-Join) |
| ORD-103 | C-20 | $350 | Growth | Matched (Inner & Left) |
| ORD-104 | C-10 | $250 | Enterprise | Matched (Inner & Left) |
Inner-joined revenue and orphan-order audit formulas
Sum only orders that resolved cleanly to a valid master-table customer record.
=FILTER(A2:D5,H2:H5,"None")Extract orphan OrderIDs missing from the Customer master (Anti-Join)
Returns "None" once all foreign keys are reconciled.
=TEXTJOIN(", ", TRUE, FILTER(A2:A5, H2:H5=FALSE, "None"))Additional worked calculation
Using the initial example inputs:
=SUMIF(H2:H5, TRUE, C2:C5)Additional worked calculation
Using the initial example inputs:
=SUMIF(D2:D5, "Enterprise", C2:C5)Inputs and interpretation
This lookup enrichment assumes unique, nonempty master keys. It is not a general SQL join: duplicate master keys would produce several SQL rows, but XLOOKUP picks one. The separate match flag identifies missing keys independently of the returned tier text. An unmatched key may reflect a typo or missing data, not a deleted customer.
Try the example
Use the original values or change them to test your understanding.