Here is a cool Filter function tip. You can learn now how to filter based on a list in another tab in Google Sheets.

You can make your Filter formula clean and readable by following my below tips and can learn some advanced Filter function use.

Do you know how to filter a dataset using criteria from a column range?

I mean, I want to use criteria in a Filter formula from a column range which can be from the same sheet tab or another sheet tab.

Please see the image below. The criteria list is in column E and the range to filter is the columns A and B.

It’s not easy to enter all the criteria within the formula because there are a total of 10 criteria aka conditions to filter. Also, it’s not suggestible as it can make your formula cluttered.

If you have 3-4 criteria, you can include it in the function very easily. I mean you can hardcode it as below.

=filter(A1:B,regexmatch(A1:A,"plum|cherry|papaya"))

But when it is very large, the better solution is to enter the criteria in a column and refer that column as criteria. Is this possible?

Yes! With the help of the REGEXMATCH function, we can do that easily.

Similar: Regexmatch in Filter Criteria in Google Sheets [Examples]

How to Filter Based on a List in Another Tab in Google Sheets

Master Filter Formula:

=filter(A1:B,regexmatch(A1:A,join("|",E1:E10)))

In my example above, the dataset (A1:B) and the criteria list (E1:E10) are on the same sheet. If the criteria list is in a different tab just include the Tab name with the range as usual.

Example:

Sheet2!E1:E10

Here in our Master Filter Formula, the range is A1:B and the criteria is the Regexmatch formula.

What does this Regexmatch formula do?

To understand that see a simple example. Suppose the value in cell A1 is “Inspired”. The following REGEXMATCH formula would return TRUE if any of the words “Info” or “Inspired” is present in cell A1.

=regexmatch(A1,"Info|Inspired")

In our main example above (mater filter formula), the criteria list is in the range E1:E10. The Join function joins all the criteria together and that separated by a pipe “|” symbol.

=join("|",E1:E10)

Output:

"apple|apricot|banana|blackberry|blackcurrant|blueberry|cherry|coconut|fig|grape"

The REGEXMATCH returns TRUE for all the matches and FALSE for the mismatch.

The Filter filters the TRUE values. That means all the values in the range A1:B filtered wherever it has a match in E1:E10.

Additional Tips

There is one unaddressed problem with the above formula. It’s the exact match.

For example, the Regexmatch formula used above can’t differentiate the word “pineapple” and “apple”. It considers both the same.

If you want an exact filter with several criteria as above, just change the formula as below.

=filter(A1:B,regexmatch(A1:A,join("|",("^"&E1:E10&"$"))))

Here what I have done is placed a Caret ^ symbol at the beginning of each criterion and a Dollar $ symbol at the end. It’s like;

^apple$|^cherry$

That’s all. Hope you have enjoyed this tutorial. Thanks for the stay.

Related:

• Filter Out Matching Keywords in Google Sheets – Partial or Full Match.
• The Alternative to SQL IN Operator in Google Sheets Query (Also Not IN)
• How to Use AND, OR with Google Sheets Filter Function – ADVANCED Use.
• How to Use Date Criteria in Filter Function in Google Sheets.
• Automatically Add Total to a Filtered Data in Google Sheets.
• Use Query Function as an Alternative to Filter Function in Google Sheets.