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!

13 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!!

  5. Greetings,

    Can the OR filter be used in a filter view, which would result in your original data set (A1:B10) being reduced to where the only viewable/visible data then becomes what you have in D1:E5 (but would appear in A1:B5)? Hopefully I’ve accurately described what I’m trying to accomplish. I have data that will potentially encompass A4:AP401 in a my spreadsheet (as more an more data gets added). I wish to filter out that data which is entered by specific people (name of person who entered the data appears in column C). That is why I am trying to create a filter view. One option is to create it via Value, whereby the people’s names can be checked off.
    The issue I have come across is that method not cumulative. What I mean is that if the data set is to be populated by Jon, Joe, Bill, Dave, Lisa and Ted, and I wish to only see the data entered by Jon and Joe, I have to create a filter view and then manually go in can check off their respective names. However, if Jon enters data, but Joe has not yet, only Jon appears as an option to check off. Which means I have to wait until Joe enters data, go back into the filter view, and check off his name, so then his subsequent data will appear. What I want to do is set up a filter view ahead of time so that whenever Jon and Joe enter data, they automatically appear in the view (without having to go back in and manipulate the filter view). Thanks for your time.

  6. Thank you for this guide! It seems to be exactly what I’m looking for, however I’m running into a bit of trouble. I’ve tried these formulas:

    =filter(Input!C8:O,(MATCH(Input!I8:I,INDIRECT(D8),0))+(MATCH(Input!I8:I,SPLIT(C8,”,”),0)))
    ^ gives me #N/A “No matches are found in FILTER evaluation”, though there should be.

    =filter(Input!C8:O,(Input!I8:I=INDIRECT(D8))+(Input!I8:I=SPLIT(C8,”,”)))
    ^ gives me #VALUE! “FILTER range must be a single row or a single column.”

    =filter(Input!C8:O,OR(MATCH(Input!I8:I,INDIRECT(D8),0)),MATCH(Input!I8:I,SPLIT(C8,”,”),0))
    ^ gives me #N/A “FILTER has mismatched range sizes. Expected row count: 994, column count: 1. Actual row count: 1, column count: 1.” (The Input sheet has 1001 rows, with data starting at row 8. The sheet with this search function is 1000 rows, and the cell where this function is being tested is C18.)

    Input!C8:O is my spread of data, Input!I8:I is a column listing specific colors. INDIRECT(D8) refers to a named range; the cell D8 has a Data Validation list of text options, and when one is selected (ex. “Greens”), INDIRECT fits it into the function to draw data from that named range (ex. Green, Emerald, Seafoam). C8 is a cell with multiple text criteria separated by commas, ex. “Red,White,Blue”. I do intend to add more criteria to search other columns, but for now the goal with this formula is to look at column I on the Input sheet and find all rows that have Red, White, Blue, *and* all Greens in column I and return that data.

    The first formula seems most promising as I can get it working exactly as intended if only one of the criteria is present (and either set of criteria works), but once I try to search for both at once, it’s errors everywhere. QUERY would probably be a better option for searching with this criteria, but it seems the data shown by QUERY doesn’t carry over hyperlinks, so I have to stick with FILTER. I can’t seem to find what I need just by Googling, so if you know of a fix or any suggestions I could try, it would be greatly appreciated!

Leave a Reply

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