Have you ever wondered how to filter data based on a list in another tab in Google Sheets? Well, I have a cool tip for you! In this article, I will show you how to use the Filter function to achieve this and make your formulas clean and readable.
The Challenge
Let’s say you have a dataset in columns A and B and you want to filter it using criteria from a column range, which could be from the same sheet tab or another tab. Entering all the criteria directly into the formula can be messy and impractical, especially when you have multiple conditions to filter.
The Solution
The good news is that you can easily solve this problem by entering the criteria in a separate column and referring to that column in your formula. How? Let me explain.
The key function to use here is REGEXMATCH. With the help of REGEXMATCH, you can check if any of the criteria in your list match a value in your dataset.
How to Filter Based on a List in Another Tab in Google Sheets
Here’s the master filter formula that you need to use:
=filter(A1:B, regexmatch(A1:A, join("|", E1:E10)))In the above formula, A1:B represents your dataset range, and E1:E10 represents your criteria list. If your criteria list is in a different tab, simply include the tab name before the range.
For example:
Sheet2!E1:E10The REGEXMATCH formula checks for matches between each value in column A and the joined criteria list.
But what does the joined criteria list look like? Let’s find out!
The join function is used to combine all the criteria in the list, separated by the pipe (|) symbol. Here’s an example of what the output might look like:
"apple|apricot|banana|blackberry|blackcurrant|blueberry|cherry|coconut|fig|grape"The REGEXMATCH function returns TRUE for all the matches and FALSE for the mismatches. The Filter function then filters the TRUE values, giving you the filtered dataset based on the criteria list.
Additional Tips
One thing to note is that the above formula does not provide an exact match. For example, it considers “pineapple” and “apple” as the same word. If you need an exact match, you can modify the formula as follows:
=filter(A1:B, regexmatch(A1:A, join("|", ("^" & E1:E10 & "$"))))In this modified formula, a caret (^) symbol is placed at the beginning of each criterion, and a dollar ($) symbol is placed at the end. This ensures that only exact matches are considered.
And that’s it! With this simple trick, you can easily filter your data based on a list in another tab in Google Sheets.
I hope you found this tutorial helpful and enjoyable. If you want to explore more tips and tricks like this, head over to Crawlan.com for more Google Sheets expertise.
Related articles from Crawlan.com:
- 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.
