Forum Discussion
Can I use wildcards with the new FILTER function
Perhaps the attached file helps you find a solution. It combines FILTER with a SEARCH for the text string in the larger text.
Your other question crossed my previous reply.
=FILTER(FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$A$2:$A$8,1))),NOT(ISNUMBER(SEARCH("TBC",FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$A$2:$A$8,1))),1))))The formula above would Find "Sponsor 1" (in C1) but would exclude "TBC".
StuLux23 Please see attached.
IMHO, one filter is enough for this formula
=FILTER($A$2:$A$8,ISNUMBER(SEARCH($C$1,$B$2:$B$8))*NOT(ISNUMBER(SEARCH("TBC",$B$2:$B$8))))- SergeiBaklan This works great for me, however if the exclusion is blank I get a #CALC! error. Can this be modified to allow for this?
Excellent, thank you very much for your help and speed of replies.
My original workbook uses VBA to create filtered lists (it was created before the FILTER command was released). Do you think that using this new formula (instead of VBA) is a better approach.
I can see the advantage that it doesn't require the user to run any code and it is always up to date but I wondered whether adding loads of additional formulas to a file might make it unstable, what are your thoughts?
