How to use an OR condition with the FILTER function

Filter with or condition

If you know the trick, 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 of the filter function?

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:

Data for 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 worksheet?

Yes, here you go.

How does this 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")

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"))

Other resources

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

Filter function in Google Sheets

Leave a Reply

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