How to Merge Two Contact Lists Without Losing Anything

Two contact lists almost never line up neatly. Maybe it's your old CRM export plus a year of open-house sign-in sheets, or the newsletter list from your website plus the one your co-founder kept in a spreadsheet. The same people are on both, under slightly different column names, with a phone number on one side and a newer address on the other.

Pasting one list under the other and hitting "remove duplicates" is quick, but it throws information away and can quietly re-subscribe people who asked you to stop. This guide walks through merging properly: lining up the columns, choosing how to match people, deciding which details win, and protecting unsubscribe status along the way.

Before you start: back up and label your sources

Save an untouched copy of each original file. Then open both lists in one workbook, each on its own tab. In this guide we'll call them ListA and ListB (no spaces in the tab names keeps the formulas simpler).

Add a Source column to each tab and fill it with something like "CRM 2024" or "Open house sign-ins". After the merge, you'll still know where every contact came from, which is useful when someone asks "how did you get my email?" and when you're deciding who gets which message.

Decide which list is your main list. It's usually the larger or better-maintained one. In our example, ListA is the main list and ListB is the one being merged into it.

Step 1: Make the columns match

Different tools export different headings for the same thing. Make a quick map before you move any data:

Rename the headings on both tabs so they're identical, and move the columns so they sit in the same order. For the rest of this guide, both tabs look like this: A Email, B First name, C Last name, D Phone, E Status, F Last updated. If one list has a single "full name" column, split it before merging; our guide to preparing a CSV for import covers that.

Also standardize the values that mean the same thing. If one list says "Yes/No" for subscribed and the other says "Subscribed/Unsubscribed", pick one wording and use Find & Replace to make them match. The formulas below look for the exact word Unsubscribed.

Step 2: Pick a match key (use the email)

To merge, you need one column that tells you two rows are the same person. Names are a poor choice: "Bob Chen" and "Robert Chen" are the same man, and there may be two different Maria Garcias. Phone numbers get formatted a dozen ways. The email address is the most reliable key for a contact list, as long as you clean it first.

On both tabs, add a Key column in G with:

=LOWER(TRIM(A2))

Fill it down. This removes stray spaces and capital letters so Bob.Chen@Northwind.com and bob.chen@northwind.com match. If many contacts use Gmail, you may also want to treat dots and +tags as the same address; our Excel duplicates guide has a formula for that.

People with no email at all can't be matched this way. Filter them out to a separate tab and handle them by hand.

Step 3: Decide which record wins

When the same person is on both lists with different details, you need rules. Write them down before you start, so you apply them the same way to every row. Sensible defaults:

• Unsubscribed always wins. If either list says the person opted out, the merged record says opted out. No exceptions.
• Something beats nothing. A blank cell should never wipe out a filled one. If ListA has no phone and ListB does, take ListB's.
• Newer beats older when both have a value and they disagree, such as two different phone numbers. Use the Last updated date.
• Keep, don't overwrite, notes and tags. If both lists have a notes column, combine them rather than choosing one.

Step 4: Protect consent and unsubscribe status

This is the part that gets people into trouble. Imagine Dana unsubscribed from your newsletter list last spring, but she's also on an older event list where she's still marked as subscribed. A careless merge keeps the event-list row, and Dana starts getting your emails again.

In the United States, the FTC's CAN-SPAM guidance says opt-out requests must be honored within 10 business days, and you can't hand off that responsibility to someone else. Other countries have their own, often stricter, rules. This isn't legal advice, but the safe habit is simple: an unsubscribe on any list applies to the merged list. Read the FTC's compliance guide for the basics, and see our guide to keeping a suppression list so the opt-outs survive future merges too.

Consent also isn't transferable between purposes. If ListB is people who signed in at an open house and never agreed to a newsletter, keep the Source column so you can treat them differently.

Step 5: Merge in Excel or Google Sheets

These formulas work in Excel and Google Sheets unless noted. Put them on the ListA tab, starting in row 2, and fill them down.

Fill in missing details from the other list

In H1 type Phone merged. In H2, if you have Microsoft 365, Excel 2021 or later, or Google Sheets:

=IF(D2<>"", D2, XLOOKUP(G2, ListB!$G:$G, ListB!$D:$D, "")&"")

In plain English: if ListA already has a phone, keep it. Otherwise, find this person's key in ListB and bring back their phone, or nothing if they're not there. The &"" at the end stops an empty ListB cell from showing up as a 0.

XLOOKUP isn't available in Excel 2019 or earlier. VLOOKUP won't help here either, because it can only return columns to the right of the match column, and our key is in G while phone is in D. Use INDEX and MATCH instead, which works in every version:

=IF(D2<>"", D2, IFERROR(INDEX(ListB!$D:$D, MATCH(G2, ListB!$G:$G, 0))&"", ""))

Repeat for any other column worth filling, such as first name or company, by changing the D references to that column.

Carry over unsubscribes

In I1 type Status merged, and in I2:

=IF(COUNTIFS(ListB!$G:$G, G2, ListB!$E:$E, "Unsubscribed")>0, "Unsubscribed", E2)

If this person appears anywhere in ListB marked Unsubscribed, the merged status becomes Unsubscribed. Otherwise, ListA's status stays.

Add the people who are only in ListB

On the ListB tab, put New? in H1 and this in H2:

=COUNTIF(ListA!$G:$G, G2)=0

TRUE means the person isn't in ListA. Filter column H to show only TRUE, copy those rows (columns A to F, plus Source), and paste them at the bottom of ListA. Fill the Key, Phone merged and Status merged formulas down over the new rows.

Lock it in

Finally, select the merged columns and paste them over themselves as values (Excel: Paste Special > Values; Sheets: Edit > Paste special > Values only). Then copy Phone merged over Phone and Status merged over Status, delete the helper columns, and you have one list.

The quick way for email lists

If what you need is a combined list of email addresses, not every column, our free Compare Lists tool does it in seconds. Paste one list on each side and choose to merge them into a single deduplicated list, see who's on both, or remove one list from another, for example taking every unsubscribe out of the merged result. It runs in your browser and nothing is uploaded. Many people use it to check the overlap first, then do the column-by-column work above only for the people on both lists.

Check the merged list before you send

Merging old and new lists tends to bring dead addresses along: people who changed jobs, closed accounts, or mistyped their email on a clipboard at an open house. Run the merged list through our typo fixer, then verify the merged list with Veml to find out which mailboxes actually exist before you email them. Veml labels each address Passed, Risky, Unknown or Invalid with a reason, and gives you a CSV with the good ones first. Pricing is pay once, with no account needed.

Merging three or more lists

Don't try to merge everything at once. Rank the lists from most trusted to least, merge the top two using the steps above, then merge the third into that result, and so on. Keep the Source column throughout, and keep a separate tab of every unsubscribe you found along the way. When the last list is in, run one final duplicate check on the Key column, and you're done.