AND/OR with a MATCH to Return True, else False -- stumped

[McNeil87]

Hello,

Am stumped with how to utilize my AND/OR's plus with a Match lookup to return True if all conditions are met, else false. Here is what I have started so far -- open to suggestions.

=IF(N2<>"COMM","TRUE",IF(OR(O2="DIP",O2="BAD",L2=550904),"TRUE",IF(ISERROR(MATCH(L2,Sheet1!$B$2:$B$36,0)),"FALSE","TRUE")))

The following conditions:

1. IF N2 = "COMM"
2. Exclude "COMM" UNLESS fits ONE criteria below:

• L2=550904
• O2=DIP or BAD
• Is located in List (using Match formula)

If ONE of the criteria in bullet points falls under "COMM" Then return "TRUE", otherwise, return "FALSE"

Thank you!

 

[etaf]

If ONE of the criteria in bullet points falls under "COMM" Then return "TRUE",

=OR( ISNUMBER(MATCH(L2,Sheet1!$B$2:$B$36,0)) , L2=550904 , O2="DIP", O2="BAD")
will return a true if any condition is found
so if Falls UNDER COMM , i assume you mean N2 = comm

then

=AND( N2="COMM", OR( ISNUMBER(MATCH(L2,Sheet1!$B$2:$B$36,0)) , L2=550904 , O2="DIP", O2="BAD") )

should return the TRUE FALSE you need

 

[DanteAmor]

IF(N2<>"COMM","TRUE"

I'm not sure what you put, does it mean that if it's different from "COMM" then you want true?

If the above is correct, check the following combinations:

varios 10mar2023.xlsm
	A											L	M	N	O	P
1
2														COMM		FALSE
3														X		TRUE
4														COMM	DIP	TRUE
5														COMM	BAD	TRUE
6												550904		COMM		TRUE
7												123		COMM		TRUE
8												200		COMM		FALSE
Sheet2

Cell Formulas
Range	Formula
P2:P8	P2	=OR(N2<>"COMM",O2="DIP",O2="BAD",L2=550904,NOT(ISNA(MATCH(L2,Sheet1!$B$2:$B$36,0))))

varios 10mar2023.xlsm
	A	B
1
2		123
3		124
4		125
5		126
6		127
7		128
8
9
Sheet1