Is it possible to do a lookup in a field that has multiple data in a cell.

Alvin Amegashie

I want to check if the emails under contact list are present in the table below.

Contact list

john.doe@abc.com,sally@abc.com,rall@abc.com

I want to check if any of those email addresses are present in another sheet the looks like this:

Email                 Status 

john.doe@abc.com       Yes

frank@abc.com          Yes

sally@abc.com          Yes

brand@abc.com         Yes

rall@abc.com            Yes

Paul Newcome

Is the contact list set up to house multiple contacts in a single cell, or are they comma separated values as in your example above?

Alvin Amegashie

The contact list is setup to separate each value by a comma

Paul Newcome

You would need to use a Multi-select Dropdown Type helper column on the Contact List sheet. Then you would put this in the helper column:

=SUBSTITUTE([Contact List]@row, ",", CHAR(10))

Then to check the box you would use:

=IF(HAS([Helper Column]@row, Email:Email), 1)