# Thread: Comparing 2 lists of names (XL XP SP-1)

1. ## Comparing 2 lists of names (XL XP SP-1)

Hello,

I've been given two files containing employee names and I want to flag exceptions (name in list A (Michael) does not match name in List B (Mike)). The problem is, one list has a more complete set of names and doing an [If a1=b1] does not take this into account and when an extra name appears, the whole list is off by one and all of the records are denoted as exceptions, even though most names are the same.

How can I compare 2 lists and just flag exceptions? Is an array formula the answer? I haven't used array formulas before.

2. ## Re: Comparing 2 lists of names (XL XP SP-1)

If you want to match column A against B1:B37, you can use
=MATCH(A1,\$B\$1:\$B\$37,0)
and fill down as far as needed. This will return the index of the matching cell or #N/A if not found.
=ISNA(MATCH(A1,\$B\$1:\$B\$37,0))
will return TRUE if the name in column A does not match any in column B, FALSE otherwise.
=IF(ISNA(MATCH(A1,\$B\$1:\$B\$37,0)),"MISMATCH","")
will return "MISMATCH" if the name in column A does not match any name in column B, blank otherwise.
Alternatively
=COUNTIF(\$B\$1:\$B\$37,A1)
will return 1 if there is a match, 0 otherwise, so
=IF(COUNTIF(\$B\$1:\$B\$37,A1)=0,"MISMATCH","") will also flag non-matching names.

3. ## Re: Comparing 2 lists of names (XL XP SP-1)

Hans,

Thank you. Take your points. You saved me a lot of time and taught me some new tricks.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•