How to search a "Lookup" value for a specific string of words found in a Google Sheet cell.

Hi there,

I am trying to create an action to send a gmail email reminder (not relevant for this question), whenever a Clio Calendar Event is about to start within 24 hours, whenever Zapier determines that “one” of the attendees associated with the Clio Calendar event is one of the named attorneys listed in Column A of a Google Sheet.

The problem is that there are usually at least two attendees per Clio event: the client and the attorney, and Clio puts them together into a single value: Attendees: Joe Schmo, Mary Foe

Joe Schmo is an attorney for this example. All attorneys and their email addresses are listed in a Google sheet. Attorney Name = Column A. Attorney Email Address = Column B. Thus:

Column A              Column B

Attorney Name   Attorney Email

Joe Schmo           joes@abc.com

Curly Schmo        curlys@abc.com

Larry Schmo         larrys@abc.com

My problem is this: How do I successfully “Lookup” and find “Joe Schmo” in the Google Sheet, when my “Lookup Value” in Clio event is not “Joe Schmo,” but is “Joe Schmo, Mary Foe.” Obviously that is not a match.

I’m trying to figure out how the Zap can determine that “Attendees: Joe Schmo, Mary Foe”  (the “Lookup Value”) actually includes/contains just the name, “Joe Schmo”, so that it finds there is a match in the Google Sheet Column A.

Right now, if I put Column A as the Lookup Column in the sheet (“Joe Schmo”), and then for the Lookup Value, I choose the Clio “Attendees: Joe Schmo, Mary Foe” value, and run the test, the test fails because the Zapier lookup is looking for a value in the Lookup column that is exactly “Joe Schmo, Mary Foe.” It is NOT looking only to see if one of the names (Joe Schmo) is in the lookup column. This is what I want it to do.

I want to set up the Google Sheets Lookup with a formula of some sort so that the zap will search for the Column A name (Joe Schmo) within the entire Lookup VAlue from Clio (e.g. Joe Schmo, Mary Foe), and upon finding that the Clio Attendees at least “contains” the attorney’s name, then Zap will consider it a scucess and will allow me to proceed with sending an email reminder to Joe Schmo at joes@abc.com.

I’ve tried Contain(), Search(), ISNUMBER (SEARCH” formulas, not knowing if any of these actually are supposed to work, and not even knowing if I am setting them up correctly. Apparently not, because they are not working the way that I’m trying to use them.

Any ideas? 

Thank you.

Bert