2

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
player0
  • 124,011
  • 12
  • 67
  • 124
Kassidy Webb
  • 23
  • 2

1 Answers1

0

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

player0
  • 124,011
  • 12
  • 67
  • 124
  • Thank you! I looks like it works, but it's acting weird & only doing like 55 at a time. I'll play around with it though now that I have a basic function for it working. Thanks again! – Kassidy Webb Sep 04 '21 at 21:32