Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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. #3
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    New York, New York, USA
    Posts
    170
    Thanks
    0
    Thanked 0 Times in 0 Posts

    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
  •