Formula to Flag names in list 1 that do not exist in list 2.

Hi all,

In the attached spreadsheets are two lists of names - note that in each list, there are duplicates - one name can appear in multiple lines.

I am hoping for some help finding a formula that will Flag (fill the cell with color) names in "Contact List 2" which do not appear in "Contact List 1". If a name in Contact List 2 meets these conditions, all instances of that name should be flagged.

Thank you so much in advance for your time! Let me know if you have any more information.

See attached file.

I've defined two named ranges, one for each list. Then I set up conditional formatting on the second list using this formula:

=AND(A2<>"",ISERROR(MATCH(A2,Contact_Name_1,0)))

giving a green background fill. I also had to change your blank entries, as they had spaces in them.

If you want it the other way round, i.e. to show names that do appear in list 1, then change the ISERROR to ISNUMBER.

Hope this helps.

Pete

Thanks to both of you, benishiryo and pete - both of these approaches are very helpful, as it is great to be able to see names that do appear in list 1 as well.