Compare names in two columns in excel worksheets

christopher ritzau 40 Reputation points
2026-10-02T17:03:18.44+00:00

Compare names in two columns in excel worksheets #excel

Microsoft 365 and Office | Excel | Other | Windows
0 comments

2 answers

Sort by: Oldest
Most helpful Newest Oldest

  1. AI answer

    2026-10-02T17:03:29.77+00:00

    To compare names in two worksheet columns and show matches, use a worksheet formula.

    If the names to compare are in Sheet1!A1:A5 and Sheet2!A1:A5, enter this in a helper cell on Sheet1, for example B1:

    =IF(ISERROR(MATCH(A1,Sheet2!$A$1:$A$5,0)),"",A1)

    Then fill the formula down.

    How it works:

    • MATCH looks for the name in A1 inside the other worksheet’s range.
    • If a match is found, the formula returns the name.
    • If no match is found, it returns a blank.

    Expected result:

    • Column B shows only the names from Sheet1 column A that also exist in Sheet2 column A.

    If the two name lists are on the same worksheet in different columns, the same pattern applies, for example comparing column A to column C:

    =IF(ISERROR(MATCH(A1,$C$1:$C$5,0)),"",A1)
    AI-generated content may be incorrect. Read our transparency notes for more information.

    Was this answer helpful?

  2. Hendrix-V 19,655 Reputation points Microsoft External Staff Moderator
    2026-10-03T00:06:52.07+00:00

    Hi christopher ritzau,

    If you're working with large lists of names, another simple option is to use Conditional Formatting to quickly highlight matches instead of returning them with a formula.

    Please try these steps:

    1. Select the names in the first column.
    2. Go to Home > Conditional Formatting > New Rule.
    3. Choose Use a formula to determine which cells to format.
    4. Enter a formula similar to: =COUNTIF($C:$C,A1)>0 (adjust the column reference as needed)
    5. Choose a highlight color and click OK.

    This will visually identify all names that also exist in the other column, which can be useful when reviewing or validating large datasets.

    I hope this provides an alternative approach for your comparison task. Let me know if you need further assistance, feel free to ask me by clicking "Add Comment" or "Add Answer" if you cannot add comment so your response will be visible. Thanks for your effort.

    Was this answer helpful?

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.