How to filter Google Sheets by multiple contains using AND

Question

I've searched for something similar but couldn't quite duplicate based on some other posts.

Column F has data on which websites you have a login with.

Let's say

F2 = Amazon, Facebook, Google, Twitter

F3 = Amazon, Google, Twitter

F4 = Amazon, Netflix, HBO

I want to filter the column to show all records that contain BOTH Amazon AND Twitter. So, the filtered results should show only F2 and F3.

Erik Tyler · Accepted Answer · 2021-01-12 02:35:43Z

1

There are many ways to do this. For simplicity's sake, I recommend this:

=FILTER(F2:F,REGEXMATCH(F2:F,"Amazon"),REGEXMATCH(F2:F,"Twitter"))

It should be easy to understand and add to as needed.

answered Jan 12, 2021 at 2:35

Erik Tyler

4,8202 gold badges6 silver badges11 bronze badges

How would I do this in the Filter by condition custom formula?
– Craig
Jan 12, 2021 at 2:51
Applied to F2:F, use this: =REGEXMATCH(F2:F,"Amazon")*REGEXMATCH(F2:F,"Twitter")
– Erik Tyler
Jan 12, 2021 at 4:37
Small error there. You only need this: =REGEXMATCH(F2,"Amazon")*REGEXMATCH(F2,"Twitter")
– Erik Tyler
Jan 12, 2021 at 10:23

Add a comment |

Marios · Accepted Answer · 2021-01-12 03:06:46Z

0

Update based on your comment:

Use this as a custom filter formula:

=REGEXMATCH(F2,"(?i)Amazon.*Twitter|Twitter.*Amazon")

As a google sheets formula:

=FILTER(F2:F,REGEXMATCH(F2:F,"(?i)Amazon.*Twitter|Twitter.*Amazon"))

answered Jan 12, 2021 at 2:40

Marios

3851 silver badge9 bronze badges

How would I do this in the Filter by condition custom formula?
– Craig
Jan 12, 2021 at 2:51
@Craig see my updated answer.
– Marios
Jan 12, 2021 at 3:06

Add a comment |

2 Answers 2