FILTER formula with exclusion list

[head]

Hi, I have searched the forum and this thread is close to what I am looking for but not exactly the same.

I am using the FILTER formula (O365) to filter a large, imported table into a new reduced table. What I need is an exclusion list where I can enter partial company names, for example “costco” rather than “Costco Wholesale Corporation”, that will be excluded from the new filtered list.

The following formula in E2 works, but only if the full name is entered into the exclusion list.

=FILTER(A2:A11,ISNA(XMATCH(A2:A11,C2:C4)))

I need the XMATCH, or another formula, to do a wildcard search that excludes rows where any text from the exclusion list is found. I’d rather not replace the main FILTER formula with something else as the actual formula includes multiple include criteria’s applied to different columns which works great. The example above is very simplified.

Thanks

 

[SanjayGMusafir]

=FILTER(A2:A11,ISNA(XMATCH(A2:A11,C2:C4)))

Try this - I think you were not using Wildcard match in XMatch. Got no data to test.

=FILTER(A2:A11,ISNA(XMATCH(A2:A11,"*"&C2:C4&"*",2)))

 

[head]

Thanks Sanjay, unfortunately no luck (I also expected that formula to work). And it does not even filter out an exact match like the original formula which is weird.

 

[SanjayGMusafir]

Thanks Sanjay, unfortunately no luck (I also expected that formula to work). And it does not even filter out an exact match like the original formula which is weird.

I had my own worries with XMatch was waiting for your reply.

Please share your sample data using XL2BB - this will save a tedious effort of recreating your sample data and then starting working with formula.

 

[Domenic]

Try...

=FILTER(A2:A11,MMULT(IF(ISNUMBER(SEARCH(TRANSPOSE(C2:C4),A2:A11)),1,0),ROW(C2:C4)^0)=0)

Hope this helps!

 

[head]

Thanks a lot Domenic! It works perfectly.

 

[Domenic]

You're very welcome, cheers!