Google-sheets – How to filter Google Sheets by multiple contains using AND

formulasgoogle sheetsgoogle-sheets-filter

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.

Best Answer

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.

Related Solutions

Google Sheets – Filter Multiple Values Using Condition Tool

You can filter using a regular expression. Use the options Filter by condition > Custom formula is...

=not(regexmatch(N:N, "(?i)facebook|pinterest|twitter"))

to see only the strings that do not contain one of the above.

Here N is the column you're filtering, and the regular expression means: the string contains match one of words facebook, pinterest, twitter, case-insensitive. And not is the negation of that.

Google Sheets – How to Live Filter Data

I would use the =filter() function as follows: on another sheet, enter

=filter(Sheet1!A:Z, Sheet1!C:C = True)

where it is assumed that the sheet with original data is Sheet1, the data is in columns A-Z, and the column with True/False values is column C.

Best Answer

Related Solutions

Google Sheets – Filter Multiple Values Using Condition Tool

Google Sheets – How to Live Filter Data

Related Topic