SOLVED

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

joeyc380_2-1625711862991.png

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

joeyc380_3-1625711875063.png

 

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:

 

joeyc380_5-1625712296008.png

joeyc380_6-1625712314042.png

 

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

joeyc380_9-1625712632841.png

joeyc380_10-1625712648008.png

 

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

@joeyc380 

 

Try it like this...

 

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

@Subodh_Tiwari_sktneer 

 

It just works like magic!

 

Thank you so much!!!

 

 

 

@Subodh_Tiwari_sktneer

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.

1 best response

Accepted Solutions
best response confirmed by joeyc380 (Copper Contributor)
Solution

@joeyc380 

 

Try it like this...

 

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

View solution in original post