Unique List, excluding certain criteria

[Rodney2485]

I need to make a New list of Pickticket's that exclude the List of Customers in Column "C"

=UNIQUE(FILTER(A:A,(A:A<>"")*(B:B<>"OZARK"))) Doesn't seem to be working

Pickticket	Customer	Exclude List	New List
5000103080	MOBILE PARTS INC.	OZARK
5000103094	MOBILE PARTS INC.	REILLY
5000103439	MOBILE PARTS INC.	O'REILLY
5000103467	MOBILE PARTS INC.	MOBILE
5000104211	JOBBERS AUTOMOTIVE WAREHOUSE INC
5000104236	CENTRAL DIESEL INC
5000104237	INTERCHANGE PARTS DIST
5000104285	PARKS AUTO PARTS INC.
5000104318	KEYSTONE AUTOMOTIVE OPERATIONS
5000104430	REGIONAL AUTOMOTIVE WAREHOUSE

[#2 · Accepted solution]

@Rodney2485 

=UNIQUE(FILTER(A2:B1000,(A2:A1000<>"")*BYROW(B2:B1000,LAMBDA(x,SUM(N(ISNUMBER(SEARCH(C2:C5,x))))=0))))

With Office 365 or Excel for the web you can apply this formula. For better performance i'd reference e.g. A2:B1000 instead of A:A since A:A refers to 1048576 rows.

[#3]

@Rodney2485 

This is pretty similar to @OliverScheurich 's solution but allows me to try our an 'eta reduced Lambda function' now that I finally have it!

= LET(
    isPresent, ISNUMBER(SEARCH(TOROW(Exclude), Table1[Customer])),
    excluded,  BYROW(isPresent, OR),
    FILTER(Table1, NOT(excluded))
  )

If eta reduction is not available, a Lambda function ORλ needs to be defined to be used in place of OR

"ORλ"
= LAMBDA(x, OR(x))