Searching column 1 for text contained in column 2 and highlighting red if NOT found

Question

I have the following example data set in excel.

Currently, for reference, John,Smith is in A4 and Smith Johnson is in B4.

Column 1 = Names of Members Column 2 = Attended (list of members that attended a meeting).

Applied to column 1, I would like to add a function that turns GREEN any members that are in column 2 (attended) that are also in column 1.

Like so:

I am essentially trying to find a quick way of producing an attendance list, and highlighting those that were not present.

The difficulty is that it is not an exact match as the usernames in the attended list may contain the surname or last name but not in the format provided in column 1.

I should also mention that the attendees list could be variable, so the formula should ideally allow for a range from say B4 to B20, even if it is left blank.

Any clever and easy solutions?

Answer 1

To highlight those members that didn't attend:

• Select A2:A9

• Use conditional formatting formula rule:

   =SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$B$5&"*",$A2)))=0
  

I usually like to tell people that they will have to be carefull using partial matches to prevent false positives. What if you have someone called "Ann" and "Anne" and "Annet" and "Annette"? Therefor, allready a bit safer could be:

=SUMPRODUCT(--(LEFT(A2,FIND(" ",$A2)-1)=$B$2:$B$5))

---

EDIT:

Based on the new data and question you can try:

• Select A4:A10

• Use conditional formatting formula rule:

    =SUMPRODUCT(--(MID($A4&" "&$A4,FIND(",",$A4)+2,FIND(",",$A4&" "&$A4,FIND(",",$A4)+2)-FIND(",",$A4)-2)=$B$4:$B$6))