How to compare two lists of email addresses in Excel worksheet?

When working with large datasets in Excel, you may encounter situations where you need to identify which email addresses from one list are also present in another. For example, you might want to match a list of registered users’ emails against a marketing campaign's contacts, or verify if attendees for an event are included in your master list. Typically, the two lists of email addresses are situated in separate columns (for instance, column A and column B), and your task is to determine which addresses appear in both columns.

In this article, you’ll find several practical solutions for comparing two columns of email addresses and identifying matches. If an email address in column B also appears in column A, you will be able to display “Match Found” alongside the relevant address in column C, or visually highlight duplicates for further review. Each method outlined here is best suited for different levels of complexity and user needs, allowing you to choose the approach that works best for your scenario.

Compare two lists of Email addresses and find the same ones with formula

Compare two lists of Email addresses and select or highlight the same ones with Kutools for Excel

Automate comparison and marking of matching emails using VBA code

Highlight matching email addresses using Conditional Formatting

---

Compare two lists of Email addresses and find the same ones with formula

One of the most direct ways to compare two columns in Excel and flag matching email addresses is by using a simple formula. This method is suitable for most routine checks, especially when the email lists are not exceptionally large. It requires no additional tools or configuration.

To identify whether an email in column B is also found in column A and display the result in column C, follow these steps:

1. In a blank cell adjacent to your first email in column B (let’s say C2 if B2 contains your first email), enter the following formula:

=IF(COUNTIF($A$2:$A$10,B2)>0, "Match Found","")

This formula checks if the email in cell B2 is present anywhere within the range A2:A10. If a match is found, it returns "Match Found"; otherwise, it returns a blank cell.

2. Press Enter to confirm the formula. Then, use the fill handle to drag the formula down through column C, so that each email in column B is checked against the list in column A. The matching results will be displayed automatically.

Make sure to adjust the ranges in the formula if your lists are longer or start in different rows. When dealing with large datasets, this method is reliable as long as your data does not have leading or trailing spaces, as these can affect matching accuracy. For best results, consider trimming spaces from your data using Excel’s TRIM function.

---

Compare two lists of Email addresses and select or highlight the same ones with Kutools for Excel

For users who frequently need to compare lists or require a more automated approach, the Kutools for Excel add-in offers the Select Same & Different Cells feature. This feature enables you to quickly select or highlight the email addresses that exist in both lists, streamlining the process significantly compared to manual formulas.

After installing Kutools for Excel, here’s how to use this feature:

1. Select the two columns you want to compare (for example, columns A and B). Then go to Kutools > Select > Select Same & Different Cells to open the dialog box.

2. In the Select Same & Different Cells dialog box, configure as follows for accurate matching:

(1.) If your data includes headers, tick the My data has headers option to prevent headers from being compared as part of your data.

(2.) Under Based on, select Each row to compare corresponding cells row-by-row or Single cell for cell-by-cell comparison, depending on your dataset format.

(3.) Under Find, choose Same values to look for duplicates appearing in both columns.

(4.) For visual emphasis, select your preferred background or font color in the Processing of results section to highlight the matched items.

3. Click OK. The email addresses found in both columns will be instantly selected and highlighted, making it easy to review or export matched results.

This approach is especially useful for large or frequently-updated spreadsheets, helping to reduce errors and save considerable time. Kutools for Excel also provides other advanced utilities for data management and comparison tasks.

Click to Download Kutools for Excel and free trial Now!

---

Automate comparison and marking of matching emails using VBA code

If you’re dealing with very large lists, require frequent repeat comparisons, or simply want to automate the comparison process, using a VBA macro is an effective option. This approach leverages Excel’s built-in programming capabilities to quickly scan both lists and mark matches programmatically. The macro below will check if each email in column B appears in column A and will write "Match Found" in the adjacent cell of column C.

VBA is particularly useful for repetitive tasks or very large datasets where manual methods may become slow or prone to oversight. However, caution is required when running VBA code—always backup your data first, and be aware that macros are only available in Excel desktop versions, not Excel Online.

1. Go to Developer > Visual Basic. In the Microsoft Visual Basic for Applications window, click Insert > Module, and paste the following code into the new module:

Sub MarkMatchingEmails()
    Dim ws As Worksheet
    Dim lastRowA As Long, lastRowB As Long, i As Long
    Dim emailA As Range, emailB As Range
    
    Set ws = ActiveSheet
    lastRowA = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
    lastRowB = ws.Cells(ws.Rows.Count, 2).End(xlUp).Row
    
    For i = 2 To lastRowB
        If Application.CountIf(ws.Range("A2:A" & lastRowA), ws.Cells(i, 2).Value) > 0 And ws.Cells(i, 2).Value <> "" Then
            ws.Cells(i, 3).Value = "Match Found"
        Else
            ws.Cells(i, 3).Value = ""
        End If
    Next i
End Sub

2. Click the Run button or press F5 to execute the code. This macro will scan all email addresses in column B (starting from row2), compare them to the list in column A, and automatically mark any matches with "Match Found" in column C.

If your email lists are not in columns A and B, modify the code accordingly. For best results, confirm columns do not contain additional blank rows within the dataset. Always save your work before running macros, and ensure that macros are enabled in your Excel settings.

---

Highlight matching email addresses using Conditional Formatting

Conditional Formatting offers a straightforward, built-in way to visually identify matching email addresses directly in your Excel worksheet, without the need for formulas or add-ins. This method is well-suited to situations where you want to quickly see at a glance which addresses are present in both lists, especially for collaborative review or presentations.

1. Select the range in column B containing your email addresses (for example, B2:B10).

2. Go to the Home tab, click Conditional Formatting > New Rule. In the dialog, select Use a formula to determine which cells to format.

3. Enter the following formula, then click Format and choose your preferred highlight color:

=COUNTIF($A$2:$A$10,B2)>0

This formula highlights cells in B2:B10 that are also found in A2:A10. Adjust the ranges as needed for your data.

4. Click OK to apply the formatting. All matching email addresses in column B will now be visually emphasized for easy identification.

If you want to highlight matches in both columns, repeat the steps for column A using the appropriate formula.

---

Troubleshooting and tips:

• For all solutions, inconsistent spacing, case sensitivity, or formatting issues in your email lists can cause unexpected results. It’s best to clean and standardize your data using Excel’s TRIM and LOWER functions before comparison.
• Large datasets can slow down manual formulas or Conditional Formatting. In such cases, using VBA or Kutools for Excel can provide better performance and flexibility.
• If you encounter errors with formulas or macros, double-check that ranges are consistent and that there are no blank rows within your selected data.

Summary suggestion: When selecting a method, consider the size of your data, your familiarity with Excel features, and how often you need to perform this task. The built-in formula and Conditional Formatting are perfect for quick reviews, while VBA and Kutools for Excel are better for automation, handling larger lists, or when consistency and repeatability are needed.