Joining two datasets by an ID requires a decision about key uniqueness. A one-result lookup can silently discard valid matches when the right-hand source contains several rows per key.
Define the relationship
Determine whether each source represents one row per customer, transaction, or another entity. Count repeated keys in both datasets before designing the join.
For a many-to-one enrichment, an exact XLOOKUP can return one attribute when the reference key is genuinely unique. A one-to-many relationship needs all matching records or an explicitly defined aggregation.
Choose the matching output
Use FILTER when all source rows for a key are needed. If a single current record is required, establish a date or version policy instead of assuming the first physical row is authoritative.
Preserve raw keys during any normalization. Check text-number differences and spaces without broadening to approximate matching. For combined keys, define boundaries unambiguously so distinct field combinations do not collide.
Audit matches and unmatched records
Keep counts for matched, unmatched, and multiply matched keys. Compare row counts and measures before and after the operation. A join can multiply amounts when both sides have repeated keys, even if each individual match looks correct.
Test a unique match, a missing key, and a repeated key deliberately. Retain an exception list rather than replacing every missing match with zero. Do not delete duplicates until their meaning has been reviewed. Document the expected relationship and reconciliation totals so future source changes cannot silently turn a previously safe lookup into an incomplete or multiplicative join.
References
Reviewed against the linked official product guidance on November 3, 2026. Features, limits, screen labels, and access can vary by account, platform, region, and organization policy.