Friday, February 6, 2015

yogi_Search For Presence Or Absence Of One Of A Specified Set of Values In A Column

             Google Spreadsheet   Post  #1889
Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     www.energyefficientbuild.com.   Feb-06-2015
post by  CaseyCain:
https://productforums.google.com/forum/#!category-topic/docs/spreadsheets/bbOjowRG9K8
Not Equals multiple values in Search


I have a few values that work just great:
=FILTER(Requests!E:E,(ISNUMBER(SEARCH("1210", Requests!L:L))))
=FILTER(Requests!E:E,(ISNUMBER(SEARCH("1220", Requests!L:L))))
=FILTER(Requests!E:E,(ISNUMBER(SEARCH("1230", Requests!L:L))))
=FILTER(Requests!E:E,(ISNUMBER(SEARCH("1708", Requests!L:L))))

I need a filter that will give me the values of E when L doesn't equal any of the above values (1210, 1220, 1230, 1708).
I've tried a few things no no avail:
=FILTER(Requests!E:E,(NE(ISNUMBER(SEARCH("1210", Requests!L:L)))))
=FILTER(Requests!E:E,<>(ISNUMBER(SEARCH("1210", Requests!L:L))))
=FILTER(Requests!E:E,(ISNUMBER(SEARCH(<>"1210", Requests!L:L))))

And similar combinations of such.
The SEARCH function is required because L doesn't just have that number in it.  So an explicit NE / <> won't work.

Please help.
---------------------------------------------------------------------------------------------------------