Can Excel =filter function include a 'criteria range' for multiple criteria of one column?

Copper Contributor

Hello Everyone,


I am trying to use  =filter formula to filter multiple criteria for a single column as below, by using a "criteria range":


However it is not working and return an #N/A



I'd like to use the =filter to return an dynamic array by using "criteria range", as if what the 'advanced filter' can do to filter multiple criteria of a particular column:





I know that =filter can do multiple criteria, by including each criteria in the formula one-by-one: 




however, comparing to a single 'criteria range/ array', this method is far too troublesome especially when there is a long list of criteria for a particular column.


Did I make any mistakes in the =filter formula with a 'criteria range'?; or the =filter formula is just not capable to do that?


Thank you!




7 Replies
best response confirmed by joeyc380 (Copper Contributor)



Try it like this...





It just works like magic!


Thank you so much!!!





Your solution is excellent

It has shortened my formula about 99%

Thank you
#2023 and still awesome answer. Thanks, exactly what i was looking for !

@Subodh_Tiwari_sktneer Holy ... your solution is amazing. I've been doing all types of crazy stuff to do what you just did so simply. You really saved me. Thanks.