How to use an advanced filter with an OR condition in Google Sheets

Advanced filter with or condition

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:

Data for advanced filter with OR example

What’s the formula?

=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))

We use a "+" for the boolean logic.

Can I see an example advanced filter worksheet?

Yes, here you go.

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:

=FILTER(A1:B10,A1:A10="alpha")

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:

=FILTER(A1:B10,(A1:A10="alpha")+(A1:A10="gamma"))

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.

Other resources

Check out my other post on more creative ways to use the FILTER function to work with data.

Filter function in Google Sheets

Have you found a use for this advanced FILTER + OR formula? Any questions? Let me know in the comments below!

9 thoughts on “How to use an advanced filter with an OR condition in Google Sheets”

  1. Hi, phenomenal walkthrough! If I wanted to refer to a cell instead typing in the text how would I go about doing that? For instance instead of “alpha” what if you wanted it to reference A1 where the “alpha” data resided? I keep getting formula parse errors. Thank you much!

    1. Hey Dan,

      You can just change the formula to this:

      =filter(A5:B14,(A5:A14=A1)+(A5:A14=B1))

      where I have my data range in A5:B14, and my filter values in A1 and B1 (e.g. alpha or beta or gamma etc.)

      Hope that helps!

      Cheers,
      Ben

  2. Ben, thanks for this. Have a question for you. What if my OR conditions were more complicated? Like, what if my filter is for multiple conditions to be met for both sides of the “or”? I’ll try to explain what I mean. For instance, what if I want it to filter something based on whether A1:A10=”alpha”, B1:B10>5, or if A1:A10=”gamma”, B1:B105)+(A1:A10=”gamma”,B1:B10<5)) but that doesn't seem to be working for me. Any help?

  3. Hi Ben

    Is it possible to set a “named range” as the filter criteria?

    Let’s say I want to use the “OR” function shown in the example, but I have a list of 50 items I want to include in my filter. Do I need to build up 50 “OR” segments in the formula, or is there a way to link to a named range?

    1. Hey Peter,

      You can use a named range in a filter, but it won’t solve this OR issue.

      I’ve not found a way to succinctly do this, other than building up the 50 OR segments. You can use a formula like this to build the 50 segments as text, which you can then copy paste into your filter formula:

      =ArrayFormula(mid(concatenate("+(A2:A11="""&transpose(unique($D$2:$D))&""")"),2,len(concatenate("+(A2:A11="""&transpose(unique($D$2:$D))&""")"))-13))

      I’ve not found a way to embed this in the filter function however.

      If I find anything, I’ll post it in the article here.

      Cheers,
      Ben

  4. Hi, Thanks for all the help, my problem it’s a little bit different, I want to syncronize a Google form with a Google Sheet, I know it is easy, but also, I want to create another sheet that automatically update the Date in one cell, for example: you send “Alpha” with the google form with a date, then you send “Beta”, Beta has his own cell, but, how do I create a Filter that update the date when you send “Beta” or “Apha”? Thanks for all the help!!

Leave a Reply

Your email address will not be published. Required fields are marked *