False positives when using IF(SUMPRODUCT(--ISNUMBER(SEARCH

[jojo12]

I am trying to check any given list of people against a master list, and highlight (via conditional formatting) any that aren't on the master list. I have a worksheet to paste a given list into, another to show all the matches and non-matches, and a third worksheet that lists the names and titles that populate the master sheet. The below formula is the only I could make work for comparing the two lists, but it is prone to producing false positives.

=IF(SUMPRODUCT(--ISNUMBER(SEARCH('MasterList'!,'ListBeingChecked'!$A;$A))),CONCATENATE("(",'Names&Titles'!$B14,")",'Names&Titles'!$C14),CONCATENATE("(",'Names&Titles'!$B14,") ",'Names&Titles'!$C14&" "))

Basically, if a given list includes "Director of Finance" and "Director of Finance" is on the master list, the formula returns "(John) Director of Finance". If the master list does not include "Director of Finance", it returns "(John) Director of Finance" (in red thanks to conditional formatting on the " " of the concatenate portion. Unfortunately, if the given list includes "Deputy Director of Finance" BUT NOT "Director of Finance", the formula returns a false positive for "Director..." because it is in the title of "Deputy Director...".

I would appreciate any help refining this to eliminate the false positives problem. Thanks!

 

[RoryA]

If you only want exact matches, don't use SEARCH, use =

There appears to be a bit missing from your formula?

 

[jojo12]

Rory,

I don't follow your suggestion about using =, could you expand on that? Also, I don't believe it is missing anything and functions properly other than the false positives. What do you think is missing?

 

[RoryA]

It appears to be missing a cell reference in the red part:
=IF(SUMPRODUCT(--ISNUMBER(SEARCH('MasterList'!,'ListBeingChecked'!$A;$A))),CONCATENATE("(",'Names&Titles'!$B14,")",'Names&Titles'!$C14),CONCATENATE("(",'Names&Titles'!$B14,") ",'Names&Titles'!$C14&" "))

 

[jojo12]

You're right, my apologies for that. I think I may have solved my problem by using something like (I don't have it in front of me currently):

=IF(ISNA(MATCH('MasterList'!$E5,'ListBeingChecked'!$A;$A,0),CONCATENATE("(",'Names&Titles'!$B14,") ",'Names&Titles'!$C14&" "),CONCATENATE("(",'Names&Titles'!$B14,")",'Names&Titles'!$C14))

I haven't fully tested this yet but it seems promising.