How to see if cells contain multiple substrings

user_8812381 0
Viewed 55

In Google Sheets (or Excel), I need to see if a column contains both of 2 substrings within 2 columns. For example:

Column 1 might have:

  • matt@gmail.com
  • matt@yahoo.com
  • doug@gmail.com
  • gmail@matt.com

Column 2 is 1 list of substrings:

  • Row 1: matt
  • Row 2: doug
  • Row 3: andrew

Column 3 is another list of substrings:

  • Row 1: gmail
  • Row 2: aol
  • Row 3: gmail

So Column 2 & 3 combined would be:

  • matt & gmail
  • doug & aol
  • andrew & gmail

I need to get Column 1 values that contain BOTH values in the same row of column 2 & 3, but in any order. So this would return TRUE for these and nothing else:

  • matt@gmail.com
  • gmail@matt.com
textjoin google-sheets match filter regex
1 Answers

try:

=FILTER(A:A, 
 MATCH(REGEXEXTRACT(A:A, TEXTJOIN("|", 1, C:C)), C:C, 0)=
 MATCH(REGEXEXTRACT(A:A, TEXTJOIN("|", 1, D:D)), D:D, 0))

enter image description here

user_56326290
Related