TL;DR:
Clean both lists first: trim spaces and lowercase the emails, or real matches will be missed.
COUNTIF in a helper column marks each row with how many times it appears in the other list.
XLOOKUP can pull extra columns from the second list when you also need the data, not just the match.
You have two exports: people on your newsletter, and people who placed an order. You want to know who is on both. This is a matching job, and it is simple once the data is clean. The usual trap is that two lists that look identical to a person do not match to a computer.
When you need this
The question comes in many forms: which attendees already have an account, which leads are already customers, which names on a mailing list are on the do-not-contact list. All of them mean the same thing, which is checking each row of list A against list B using a shared key.
Choose the key
The key is the column you use to decide that two rows are the same person. An email address is usually better than a name, because many people share names and nobody else shares your email address. A customer ID is better still when both lists have one.
Clean first
Matching compares exact text. A trailing space or different capitals can make a real match look like a miss. COUNTIF and XLOOKUP ignore capital letters, but neither ignores spaces. So make a clean copy of the key column in each list.
=TRIM(LOWER(B2))Put that formula in a new column next to the emails, fill it down, and match on the clean column. If you need to remove the formulas later, copy the clean column and paste it back as values.
Worked example: matching emails between two exports
List A is the newsletter. List B is the orders. The raw values look like this.
List A: newsletter | List B: orders (raw) | List B clean |
|---|---|---|
ana@example.com | Ana@Example.com | ana@example.com |
ben@example.com | chloe@example.com | chloe@example.com |
chloe@example.com | eli@example.com | eli@example.com |
dev@example.com | ben@example.com (with a trailing space) | ben@example.com |
List A's emails are in A2:A5. The cleaned List B emails are in D2:D5. In B2, mark whether each List A email appears in List B:
=IF(COUNTIF($D$2:$D$5, A2) > 0, "In both", "Only in A")COUNTIF counts how many cells in the clean List B equal the email in A2. If it is more than zero, the customer appears in both. Fill it down and read the result.
Email in A | Count in B | Result |
|---|---|---|
ana@example.com | 1 | In both |
ben@example.com | 1 | In both |
chloe@example.com | 1 | In both |
dev@example.com | 0 | Only in A |
Check by eye: Ana, Ben and Chloe appear in both, which is 3 people. Dev is only on the newsletter. Eli is only in the orders list, so he does not show up in this check. To find people in B who are not in A, repeat the formula the other way round, with the ranges swapped.
Without cleaning, the raw List B would still match Ana, because COUNTIF ignores capitals, and Chloe. But Ben's trailing space would make his email different text, so he would be reported as only in A. That one hidden space is exactly why you clean first.
Using XLOOKUP when you want more than a yes or no
When you also need information from the second list, such as the order total, XLOOKUP looks up a value and returns the matching item from another column. Google Sheets has it, and so do newer versions of Excel; older Excel does not.
=XLOOKUP(A2, $D$2:$D$5, $E$2:$E$5, "No order")Here E holds the order total. The last argument is what to show when there is no match. It returns the order total for each person who bought, and No order for the rest. If your Excel is older, a VLOOKUP-style lookup does the same, as does INDEX with MATCH.
Mark matches so you can act on them
Filter the helper column by In both, then copy those rows to a new tab.
Add a conditional formatting rule that shades the In both rows, which is handy for a quick look.
Keep the helper column in your file, so anyone can see how the result was reached.
Record the date of the exports, since both lists change over time.
Mistakes to avoid
Matching on names. Two different people called Ana Lopez will look like one person.
Comparing unlike things. A column of numbers and a column of text with the same digits will not match.
Partial ranges. If your second list has 4,000 rows and the formula covers 400, the rest are reported as missing. Check the range end.
Treating a duplicate as a second person. If an email appears twice in List B, COUNTIF returns 2, which still shows as a match. If you need the number of people, remove duplicates first.
Skipping the sanity check. Count the matches two ways, once with a filter and once with the formula, and compare.
Doing it faster
For a repeating task, keep the clean and marking formulas in a template tab and paste fresh exports into it. If you want help with the formula on your own sheet, press DuckyHelper's key and ask out loud while it looks at your screen; keep real customer data in mind, and only share what you are comfortable having on screen.
Frequently asked questions
Does COUNTIF care about capital letters?
No. It matches Ana@Example.com with ana@example.com. It does care about spaces.
What if my emails contain a star or a question mark?
They are rare, but COUNTIF treats them as wildcards. In that case, use XLOOKUP, which does not treat them that way by default.
Get started
