SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2526472%22%20slang%3D%22en-US%22%3ECan%20Excel%20%3Dfilter%20function%20include%20a%20'criteria%20range'%20for%20multiple%20criteria%20of%20one%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526472%22%20slang%3D%22en-US%22%3E%3CP%3EHello%20Everyone%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20use%26nbsp%3B%20%3Dfilter%20formula%20to%20filter%20multiple%20criteria%20for%20a%20single%20column%20as%20below%2C%20by%20using%20a%20%22criteria%20range%22%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_2-1625711862991.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294189iD1450675A8B8152C%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_2-1625711862991.png%22%20alt%3D%22joeyc380_2-1625711862991.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EHowever%20it%20is%20not%20working%20and%20return%20an%20%23N%2FA%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_3-1625711875063.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294190iFCE71684D9BF5E40%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_3-1625711875063.png%22%20alt%3D%22joeyc380_3-1625711875063.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI'd%20like%20to%20use%20the%20%3Dfilter%20to%20return%20an%20dynamic%20array%20by%20using%20%22criteria%20range%22%2C%20as%20if%20what%20the%20'advanced%20filter'%20can%20do%20to%20filter%20multiple%20criteria%20of%20a%20particular%20column%3A%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_5-1625712296008.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294192i6ACA495541536271%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_5-1625712296008.png%22%20alt%3D%22joeyc380_5-1625712296008.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_6-1625712314042.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294193i1ED3D9E6326B25E6%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_6-1625712314042.png%22%20alt%3D%22joeyc380_6-1625712314042.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20know%20that%20%3Dfilter%20can%20do%20multiple%20criteria%2C%20by%20including%20each%20criteria%20in%20the%20formula%20one-by-one%3A%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_9-1625712632841.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294196iF242B9F996EDDA08%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_9-1625712632841.png%22%20alt%3D%22joeyc380_9-1625712632841.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22joeyc380_10-1625712648008.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F294197i412EC3B810AB3D42%2Fimage-size%2Fmedium%3Fv%3Dv2%26amp%3Bpx%3D400%22%20role%3D%22button%22%20title%3D%22joeyc380_10-1625712648008.png%22%20alt%3D%22joeyc380_10-1625712648008.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3Ehowever%2C%20comparing%20to%20a%20single%20'criteria%20range%2F%20array'%2C%20this%20method%20is%20far%20too%20troublesome%20especially%20when%20there%20is%20a%20long%20list%20of%20criteria%20for%20a%20particular%20column.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDid%20I%20make%20any%20mistakes%20in%20the%20%3Dfilter%20formula%20with%20a%20'criteria%20range'%3F%3B%20or%20the%20%3Dfilter%20formula%20is%20just%20not%20capable%20to%20do%20that%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2526472%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EOffice%20365%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526496%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20Excel%20%3Dfilter%20function%20include%20a%20'criteria%20range'%20for%20multiple%20criteria%20of%20one%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526496%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098208%22%20target%3D%22_blank%22%3E%40joeyc380%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ETry%20it%20like%20this...%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-applescript%22%3E%3CCODE%3E%3DFILTER(F10%3AI21%2CISNUMBER(MATCH(F10%3AF21%2CF26%3AF28%2C0)))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2526503%22%20slang%3D%22en-US%22%3ERe%3A%20Can%20Excel%20%3Dfilter%20function%20include%20a%20'criteria%20range'%20for%20multiple%20criteria%20of%20one%20column%3F%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2526503%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F394231%22%20target%3D%22_blank%22%3E%40Subodh_Tiwari_sktneer%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIt%20just%20works%20like%20magic!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20so%20much!!!%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New 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!

 

 

 

3 Replies
best response confirmed by joeyc380 (New 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!!!