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!
=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!