1

I'm wanting to create a list of strings to search for in Column A. Example "Cafe", "Restaurant", "Shop X"...

Then if a string in another cell (example: "Robert Harris Cafe") contains any of the values in column A, I want it to return true.

Similar to COUNTIF but but with a REGEXMATCH. Count it if it contains any of those values in column A.

1
  • Welcome to Web Applications Stack Exchange. It is unclear whether the desired result should look like true, or like 3, or like Cafe, Shop, Service. Please edit your question and insert a table of sample data together with another table that shows your manually entered desired results. Jan 5 at 8:12

1 Answer 1

1

To get a true/false result:

=0 < counta( 
  iferror( 
    filter( 
      A2:A, 
      search(A2:A, B2) 
    ) 
  ) 
)

To get a count:

=counta( 
  iferror( 
    filter( 
      A2:A, 
      search(A2:A, B2) 
    ) 
  ) 
)

To list the keywords that match:

=filter( 
  A2:A, 
  search(A2:A, B2) 
)
1
  • Thank you so much!! Works perfect! True or false is all i was after but those are all great to know! Jan 5 at 8:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.