One Filter Function as the Criteria in Another Filter Function in Google Sheets

Have you ever wondered how to use the output of one filter function as the criteria in another filter function in Google Sheets? Well, you’re in luck! In this article, we’ll explore this powerful technique that can help you filter your data more efficiently.

How to Use Filter Function Output as the Criteria in Another Filter Function in Google Sheets

Filtering a dataset in Google Sheets can be done in various ways, but two popular methods are using the Slicer and Create a filter options under the Data menu, or using the Query and Filter functions. While Query is more advanced, Filter is known to be more resource-friendly and is recommended whenever possible.

Let’s dive into a practical example to illustrate how this technique works. Imagine you have two tables: Table 1, which contains a list of fruits and their quantity, and Table 2, which contains only unique fruits along with their unit price.

The goal is to filter Table 1 based on the unit price in Table 2. For instance, you may want to filter the fruits in Table 1 if their unit price in Table 2 is less than a certain value, such as less than 5.

To accomplish this, we can start by filtering Table 2 using the criteria of unit price < 5. If the filter returns a single fruit name, we can directly use it as the condition in the second filter formula to filter Table 1. However, if the filter returns multiple fruit names, we need to use additional functions like JOIN/TEXTJOIN and REGEXMATCH.

Here’s a step-by-step breakdown of the process:

1. Filter Table 2 to retrieve the fruits with a unit price less than 5.
2. If the filter returns a single fruit name, use it as the condition in the second filter formula.
3. If the filter returns multiple fruit names, use JOIN/TEXTJOIN and REGEXMATCH to refine the condition.
4. Apply the refined condition as the criteria in the second filter formula.

Let’s take a closer look at the formulas involved:

Formula #1:

=filter(A3:B, A3:A = "watermelon")

This formula filters Table 1 based on the condition that the fruit name matches “watermelon”.

Formula #2:

=filter(D3:D9, E3:E9 < 2)

This formula filters Table 2 to retrieve the item “watermelon” with a unit price less than 2.

Formula #3:

=filter(A3:B, A3:A = filter(D3:D9, E3:E9 < 2))

Finally, this formula uses the output of the inner filter function as the condition in the second filter formula to filter Table 1.

It’s important to note that when using the inner filter function output as the criteria in another filter function, we need to refine the inner filter output to exclude any blank cells. We can achieve this by using the following filter formula as the criteria:

Formula #5:

=filter(D3:D, (E3:E < 2) * (E3:E <> ""))

Applying this refined condition to our previous example, the final formula would be:

Formula #6:

=filter(A3:B, A3:A = filter(D3:D, (E3:E < 2) * (E3:E <> "")))

Now, let’s discuss the scenario where we have multiple conditions in the inner filter formula. For instance, if we want to filter fruits in Table 1 with a unit price less than 5, we can modify the inner filter formula as follows:

Formula #7:

=filter(D3:D, (E3:E < 5) * (E3:E <> ""))

However, if we use this formula as the criteria in another filter formula, we’ll encounter an #N/A! error. To solve this, we can make use of the REGEXMATCH and JOIN functions in Google Sheets.

Here’s a step-by-step breakdown of the modified process:

1. Combine the multiple criteria using the JOIN function.
2. Replace the original condition in the second filter formula with the REGEXMATCH and JOIN combination.
3. Apply the modified condition as the criteria in the second filter formula.

For example, the modified condition would look like this:

Formula #10:

=filter(A3:B, REGEXMATCH(A3:A, textjoin("|", true, FILTER(D3:D, E3:E < 5))))

By using this final formula, we can effectively filter Table 1 based on multiple conditions.

That’s all there is to it! Now you know how to use one filter function output as the criteria in another filter function in Google Sheets. This technique will undoubtedly help you filter your data more efficiently and effectively.

For more resources and tips on how to make the most out of Google Sheets, head over to Crawlan.com. Stay tuned for more juicy secrets and happy filtering!