How to join two lists on email addresses safely

Why raw email joins miss matches

Email looks like a perfect key: unique per person and present in CRM exports, payment systems and newsletter lists. In practice the same person appears as Sam.Lee@Example.com, sam.lee@example.com (trailing space) and samlee+shop@gmail.com. An exact lookup treats each as different, and Excel's lookups are case-insensitive while Power Query and most SQL comparisons are case-sensitive, so the same data gives different counts depending on the tool.

Step 1: safe normalisation everyone agrees on

These changes never merge two different mailboxes in practice, so apply them to both lists:

• Trim leading and trailing spaces, and remove non-breaking spaces (CHAR(160)) that come from web copy-paste.
• Lower-case the whole address. Technically the part before @ can be case-sensitive, but virtually no real mail provider treats it that way.
• Strip wrappers such as Sam Lee <sam@example.com> or mailto: prefixes.
• Blank out invalid values like “n/a”, “none” or addresses without an @, so they don't all match each other.

=LOWER(TRIM(SUBSTITUTE(A2, CHAR(160), " ")))

lower(trim(replace(email, chr(160), ' '))) AS email_key

Step 2: aliases, only where the provider supports them

Gmail ignores dots in the local part and anything after a plus sign, and treats googlemail.com as gmail.com. So j.patel+news@gmail.com and jpatel@gmail.com reach the same inbox. Many other providers support plus addressing, but dot handling differs, and on company domains a dot can separate two different people (j.smith vs jsmith). Apply alias rules per domain, not globally:

CASE
  WHEN split_part(e, '@', 2) IN ('gmail.com', 'googlemail.com')
    THEN replace(split_part(split_part(e, '@', 1), '+', 1), '.', '') || '@gmail.com'
  ELSE e
END AS email_key

Keep a flag showing which rows matched only because of alias handling, and review them. Two genuinely different customers sharing an alias-collapsed key is rare, but when it happens the merged record mixes their orders.

Step 3: join, then check both directions

1. Count duplicates of the key in each list (COUNTIF or GROUP BY email_key HAVING count(*) > 1). Shared inboxes such as info@ or accounts@ often belong to several contacts.
2. Join on the key with a full outer join, or XLOOKUP both ways, so you see unmatched rows on each side.
3. Report counts: matched, CRM only, billing only. The totals should add back to each list's distinct keys.

Here the CRM has 812 + 37 + 371 = 1,220 distinct keys (20 duplicates removed) and billing has 812 + 37 + 131 = 980. For deduplicating a combined list afterwards, see merging contact lists without duplicates; for a worked CRM-to-payments case, see matching HubSpot contacts to Stripe customers.

Privacy: email is personal data

Under UK GDPR and similar laws an email address that identifies a person is personal data, so joining two contact lists is processing that needs a purpose and a lawful basis. A few habits keep it contained:

• Join only the columns you need. Drop phone numbers and addresses from the copy you match on.
• Don't upload customer lists to random online “merge” tools whose storage you can't check.
• If two teams must match lists without exchanging raw emails, both can hash the normalised key (for example SHA-256) and compare hashes. Hashing hides the address from a casual reader but common emails can still be guessed, so treat the hashes as personal data too.
• Delete working copies when the job is done.

Analistable reads both files into DuckDB inside your browser, so the contact lists aren't uploaded to or stored on a server, and you can ask “which billing customers aren't in the CRM?” directly. More techniques are in the join spreadsheets guide.

A repeatable routine

1. Copy each list and keep only an ID, the raw email and the fields you need to compare.
2. Add an email_key column with the safe normalisation, then a second column applying alias rules for gmail.com and googlemail.com only.
3. Mark blank, invalid and shared-inbox keys so they are excluded from automatic matching.
4. Join on the key, label each matched row with the rule that produced the match, and export the match summary.
5. Have someone review the alias-only matches and any duplicates, then apply the result to the source system by hand or by import.

Written down, this takes a few minutes each time and gives the same answer whoever runs it, which matters when marketing and finance compare counts.

Frequently asked questions

What should I do with shared inboxes like info@ or accounts@?

Exclude them from automatic matching. Several people or companies can share one, so match those rows on another field such as company name or customer number, or review them by hand.

How do I match emails when one list has names and emails in one cell?

Extract the part between < and > with TEXTBEFORE and TEXTAFTER in Microsoft 365, or a regular expression in SQL, then normalise it as usual.

Is XLOOKUP case-sensitive for emails?

No. XLOOKUP, VLOOKUP and MATCH ignore case, but Power Query merges and most SQL comparisons don't, so lower-case both sides to get the same result everywhere.

Should I remove dots from all email addresses?

No. Only Gmail-style providers ignore dots. On other domains, dots can distinguish different people, so apply that rule to gmail.com and googlemail.com only.

How do I find emails with hidden spaces?

Compare LEN(A2) with LEN(TRIM(A2)), or use =CODE(MID(A2, n, 1)) to spot CHAR(160) non-breaking spaces copied from web pages.

Is hashing emails enough to make matching anonymous?

Not fully. Hashed emails can be matched against guesses of common addresses, so they're usually still treated as personal data.

Related guides

• How to merge contact lists without duplicates
• How to match HubSpot contacts to Stripe customers
• How to join spreadsheets on a common column
• How to join on the closest match with an approximate lookup
• How to join two tables on two columns at once