Filter Help

[J_Wechsler]

Hello,

I am trying to use filter function with criteria to exclude a range of words.

The formula =UNIQUE(FILTER('DATA,(ISNUMBER(SEARCH($O$18,DATA)))*(DATA<>""))) works to exclude the value in cell O18 but I am trying to also exclude values in cells O19, O20, etc

Any ideas?

[#2]

Where is the Excel file?

[#3]

first of all this assumes DATA is a single column of values and based on what you are explaining it is doing the opposite of what you want because it is including only rows that it CAN find $O$18 and that cell is not blank.
So first off you need to realize that the FILTER function will look at rows of data and assess if that row should be included based on the given criteria. so something like:
=UNIQUE(FILTER(data,1-((ISNUMBER(SEARCH($O$18,data)) + ISNUMBER(SEARCH($O$19,data)) + ISNUMBER(SEARCH($O$20,data))>0) ),"none"))
this will look for o18 OR o19 OR o20 and then EXCLUDE them
NOTE this is based on searching TEXT and that the value in O18-20 could also be a subset of the value in the DATA range i.e. "sam" would exclude Sam AND Samantha AND Hasamda

[#4]

@J_Wechsler 

That could be

with

=UNIQUE(
   FILTER( data,
           TRANSPOSE( --NOT( MMULT( SEQUENCE(, ROWS( $O$18:$O$20), 1, 0),
                                   --ISNUMBER(SEARCH(  $O$18:$O$20, TRANSPOSE(data)) ) )
                        )
           )
) )

[#5 · Accepted solution]

@J_Wechsler forgot to exclude blanks, but that easy to add

[#6]

@Sergei Baklan Thanks. Can I trouble you to add in excluding blanks?

[#7]

@J_Wechsler 

Like this

with

=UNIQUE(
   FILTER( data,
           TRANSPOSE( --NOT( MMULT( SEQUENCE(, ROWS( $O$17:$O$19), 1, 0),
                                     --ISNUMBER(SEARCH(  $O$17:$O$19, TRANSPOSE(data)) ) )
                                        )
           ) * ( data <> "")
) )

[#8]

Works great! Thank you

[#9]

@J_Wechsler , you are welcome, glad to help