If you know the trick for this advanced filter, this is quite easy to do, but if you don’t, well…
…then it’s easy to get lost in nested OR functions inside of FILTER functions and end up stranded in the land of errors.
How can I use OR logic inside an advanced filter?
In this example I have 10 rows of data in columns A and B, as follows, and I want to retrieve all of the “alpha” and “gamma” values.
Rather than running two separate filter formulas, and then combining the results, it would be nice to be able to write one formula that gives us the desired output.
Thankfully, it’s possible!
Begin with this dataset for our advanced filter example:
What’s the formula?
We use a
"+" for the boolean logic.
Can I see an example advanced filter worksheet?
How does this advanced filter formula work?
Consider a basic FILTER function like this one, for example, which returns only data with “alpha” in column A:
Now, let’s see how the advanced filter works.
The next step is to return all the data that matches “alpha” or matches “gamma”.
We do this by using a
"+" as the boolean operator OR inside the second argument of the FILTER function, as follows:
Now, anytime either condition of the filter is satisfied, in other words an “alpha” or a “gamma” in column A is present, then that row of data is returned in our output filtered dataset.
Have you found a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments below!