SOLVED

New Contributor

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

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!

5 Replies
best response confirmed by joeyc380 (New Contributor)
Solution

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

Try it like this...

``=FILTER(F10:I21,ISNUMBER(MATCH(F10:F21,F26:F28,0)))``

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

It just works like magic!

Thank you so much!!!

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

You're welcome @joeyc380! Glad it worked as desired.

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

@Subodh_Tiwari_sktneer