So I have a cell C4 in which I have an item list for a data validation. Below, I want to create a filter that will take all the entries in another sheet named All and compare that to the C4 cell.

So far I have this:

=Filter(All!A2:U999, All!C2:C999 = C4)

And it works. However, I also want to add an option to ignore the C4 cell. So, in my data sheet I can have the values of A,B,C,D or E, and by using the filter method above, I can successfully filter out all entries of the same type. But in the C4 cell I have an extra value: "Any".

How can I modify the filter formula so that when the "Any" filter is selected, the filter shows me all the entries from my data sheet?

In laymans terms, I want something like this.

IF(C4 <> "Any") THEN
    Only show entries that are equal to C4
    Show all entries

Keep in mind that I want to do this multiple times for the same filter, with more C4-like cells for a more accurate search, so the formula must be expandable.

1 Answer 1


Seems like the answer was pretty easy. Managed to figure it out myself.

=FILTER(All!A2:C999, All!C2:C999 = IF(C4 = "Any", All!C2:C999, C4))

If C4 is set to Any, then the filter allow all of them since every cell will be equal to itself. If its not, it only passes the ones which are equal to the C4 value.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.