Unique Filter with several OR and AND conditions

Question

I'm trying to get a list of Unique Values with based on different criteria.

1100 | IT5   | Evo | A  
1200 | IT5   | Bug | A
1300 | IT5   | Evo | E
1300 | IT5   | Evo | E
1400 | IT5.2 | Iss | Z
1400 | IT5.2 | Iss | Z
1400 | IT5.2 | Iss | Z
1500 | IT1   | Bug | B
1600 | UUAA  | Bug | C
1600 | UUAA  | Bug | C
1700 | IT3   | Evo | D
1800 | UI    | Iss | B
1800 | UI    | Iss | B
1900 | IT2   | Iss | A
2000 | IT5   | Iss | T
2100 | IT5   | Bug | T

Expected result is

1200
1400
1800

To summarize, record need to be retreived if

• column B contains IT5 or IT5.2 or UUAA or UI

• AND column C does not contain Evo

• AND column D contains A or B or R or S or Z

I'm currently using follwing filter : =UNIQUE(FILTER($A$3:$A$1000;($B$3:$B$1000="IT5")*($C$3:$C$1000<>"Evo")))

However, some records are missing because I dont how to write other filters ...

Thank you in advance

Answer 1

I think your expected results are a bit wrong. Try:

=UNIQUE(FILTER(A1:A16,ISNUMBER((MATCH(B1:B16,{"IT5.2","IT5","UUAA","UI"},0)*(MATCH(D1:D16,{"A","B","R","S","Z"},0)*(C1:C16<>"Evo"))))

Answer 2

Use + for OR and * for AND

=UNIQUE(FILTER($A$3:$A$1000;(($B$3:$B$1000="IT5")+($B$3:$B$1000="IT5.2")+($B$3:$B$1000="UUAA")+($B$3:$B$1000="UI"))*($C$3:$C$1000<>"Evo")*(($D$3:$D$1000="A")+($D$3:$D$1000="B")+($D$3:$D$1000="R")+($D$3:$D$1000="S")+($D$3:$D$1000="Z"))))