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!

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

  7. Hi Ben,

    Thank you for the article. I have followed your example however it still is coming back with an error. My formula is

    "=FILTER('House Buying Analysis'!A:V,'House Buying Analysis'!(D:D="Richmond") + (D:D="Burnley"))"

    The error is “Formula Parse Error”.

    I was just wondering if you had any idea why it is not working? Thank you in advance!

    1. Hey Cody,

      The issue is with how you’ve written the ranges in the filter criteria, because they’re presumably in a different tab than your formula. Try this version:

      =FILTER(‘House Buying Analysis’!A:V,(‘House Buying Analysis’!D:D=”Richmond”) + (‘House Buying Analysis’!D:D=”Burnley”))

      Cheers,
      Ben

  8. HI dan-
    This is very helpful. I’m having trouble however trying to find the mode of one column based on filters of other columns.
    For example I need to find the mode of Column N, based on the contents of column X, N and B.
    B=1
    N=Stores
    X=discounted
    Y=values of discount (for which i need the mode.)

    Do you have any suggestions? Thank you so much in advance 🙂

  9. I HAVE A TWO SHEETS ONE IS CUSTOMER SHEET AND OTHER IS CODE WISE PRICE DETAILS .I WANT TO SEARCH RECORD BASED ON A CUSTOMER AND CODE PLZ GIVE DETAILS OF FUNCTION.

  10. With regard to using a named range or indeed CSV text as filter values rather than hard coding them as above I enclose three slightly different variations on the OR filter idea without the OR!, rather using MATCH instead which would seem to be the more obvious approach since it is a matching exercise.
    The suggested solution may be found at
    https://docs.google.com/spreadsheets/d/1gKJPJeN7DHNfSO1F5Qv3u1pf9PY484ceEIiQr5MVdeM/edit?usp=sharing
    Regards.
    John B

  11. I’m curious how one would do this on an importrange logic in another workbook like this:
    =FILTER(IMPORTRANGE(“sheetkey”, “Checks”), INDEX(IMPORTRANGE(“sheetkey”, “Checks”),0,8)=”ABC”)

  12. Hi Ben,

    The data is filtering in perfectly except that once it is filtered, I am having a problem filtering the data A-Z or Z-A.

    For example, I am using a master spreadsheet with data headers “Name, Venue, Time, Date, Review.” From that master spread sheet I am filtering data by Name into individual sheets. Example sheet 1 is “Joe Smith” and all “Joe Smith” data is filtered from master sheet to sheet 1. Once all that data is filtered in, I am having difficulty filtering “Venue” from A-Z or Z-A. I have set up the filter on sheet 1 but it does not perform the function when commanded. Any help???

  13. Ben Collin,

    Congratulations for you expetise, dedication e colaboration with the community.
    In due course, I will post an issue where your experience could be of extreme value.

  14. Hi Ben,

    Is there a walk around where we multiple conditions are optional and not always selected in drop down.
    Example for filtering student data
    Data validation A1 is Age, B1 Class, C1 City, D1 Gender
    =FILTER(Data!A:D,(Data!A:A=A1)+(Data!B:B=B1)+(Data!C:C=C1)+(Data!D:D=D1))
    can we get results even when all 4 options are not selected matching any 1 or all can filter results

Leave a Reply

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