IF nested query

[Deelof]

What's the best formula for the below criteria to return specific text as the Action column?

If "On Web" and Stock is less than or equal to 2 and Core or Non Core = "False", "Remove from Web"
And
If "On Web" and Stock is equal to 0 but Core or Non Core = "True", "Leave on Web"
And
If "Not on Web", stock is greater than 2 - "Data Checks"
And ignore any blanks in the Web Status column, returning a blank Action

WEB STATUS	CORE	NON CORE	STOCK	ACTION
ON WEB	FALSE	FALSE	2	REMOVE FROM WEB
ON WEB	FALSE	FALSE	5	LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	1	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB
ON WEB	FALSE	FALSE	0	REMOVE FROM WEB
ON WEB	FALSE	FALSE	10	LEAVE ON WEB
NOT ON WEB	FALSE	FALSE	32	DATA CHECKS
NOT ON WEB	FALSE	FALSE	20	DATA CHECKS
ON WEB	TRUE	FALSE	0	LEAVE ON WEB
ON WEB	FALSE	FALSE	6	LEAVE ON WEB
ON WEB	FALSE	FALSE	12	LEAVE ON WEB
ON WEB	FALSE	FALSE	7	LEAVE ON WEB
ON WEB	TRUE	FALSE	1	CORE - LEAVE ON WEB
ON WEB	TRUE	FALSE	9	CORE - LEAVE ON WEB
ON WEB	FALSE	TRUE	0	NON CORE - LEAVE ON WEB

 

[kevin9999]

I don't believe any of your rows satisfy the requirement for "Leave on Web", because none of the rows with "ON WEB" in web status has a stock level of 0 and the logic of the first test
and Core or Non Core = "False", "Remove from Web"
already eliminates the next option.

If "On Web" and Stock is equal to 0

Therefore, here's an option with your current logic:

Book1
	A	B	C	D	E
1	WEB STATUS	CORE	NON CORE	STOCK	ACTION
2	ON WEB	FALSE	FALSE	2	REMOVE FROM WEB
3	ON WEB	FALSE	FALSE	5
4	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
5	ON WEB	FALSE	TRUE	1	REMOVE FROM WEB
6	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
7	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
8	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
9	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
10	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
11	ON WEB	FALSE	FALSE	0	REMOVE FROM WEB
12	ON WEB	FALSE	FALSE	10
13	NOT ON WEB	FALSE	FALSE	32	Data Checks
14	NOT ON WEB	FALSE	FALSE	20	Data Checks
15	ON WEB	TRUE	FALSE	0	REMOVE FROM WEB
16	ON WEB	FALSE	FALSE	6
17	ON WEB	FALSE	FALSE	12
18	ON WEB	FALSE	FALSE	7
19	ON WEB	TRUE	FALSE	1	REMOVE FROM WEB
20	ON WEB	TRUE	FALSE	9
21	ON WEB	FALSE	TRUE	0	REMOVE FROM WEB
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=IF(AND(A2="ON WEB",OR(B2=FALSE,C2=FALSE),D2<=2),"REMOVE FROM WEB",IF(AND(A2="ON WEB",OR(B2=TRUE,C2=TRUE),D2=0),"Leave on Web",IF(AND(A2="NOT ON WEB",D2>2),"Data Checks","")))

 

[Deelof]

Thanks but your suggestion is not quite working as column C = TRUE but action results in Remove from Web?

WEB STATUS	CORE	NON CORE	STOCK	ACTION
ON WEB	FALSE	TRUE	0​	REMOVE FROM WEB

 

[kevin9999]

Because your original post states that if either column B OR C contains FALSE - then it should be Remove from Web, and in your example column B is FALSE. I think this is going to boil down to which 'set' of conditions/tests takes precedence.

 

[Deelof]

Ah I see. So how should I adapt if either B or C contains a TRUE to not return Action of Remove from Web or if both state FALSE and 0 stock the Remove from Web? Any suggestions

 

[Muhammad_Usman]

Receip.xlsm
	A	B	C	D	E	F
1	WEB STATUS	CORE	NON CORE	STOCK	ACTION	Formula
2	ON WEB	NO	NO	2	REMOVE FROM WEB	Remove From Web
3	ON WEB	NO	NO	5	LEAVE ON WEB	Leave On Web
4	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
5	ON WEB	NO	Yes	1	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
6	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
7	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
8	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
9	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
10	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
11	ON WEB	NO	NO	0	REMOVE FROM WEB	Remove From Web
12	ON WEB	NO	NO	10	LEAVE ON WEB	Leave On Web
13	NOT ON WEB	NO	NO	32	DATA CHECKS	Date Checks
14	NOT ON WEB	NO	NO	20	DATA CHECKS	Date Checks
15	ON WEB	NO	NO	3	LEAVE ON WEB	Leave On Web
16	ON WEB	NO	NO	6	LEAVE ON WEB	Leave On Web
17	ON WEB	NO	NO	12	LEAVE ON WEB	Leave On Web
18	ON WEB	NO	NO	7	LEAVE ON WEB	Leave On Web
19	ON WEB	Yes	NO	1	CORE - LEAVE ON WEB	Core - Leave on Web
20	ON WEB	Yes	NO	9	CORE - LEAVE ON WEB	Core - Leave on Web
21	ON WEB	NO	Yes	0	NON CORE - LEAVE ON WEB	Non Core - Leave on Web
Sheet20

Cell Formulas
Range	Formula
F2:F21	F2	=IF(AND(A2="On Web",D2<=2,B2="No",C2="No"),"Remove From Web",IF(AND(A2="On Web",D2>0,B2="No",C2="No"),"Leave On Web",IF(AND(A2="Not On Web",D2>2),"Date Checks",IF(AND(A2="On Web",D2<2,B2="No",C2="Yes"),"Non Core - Leave on Web",IF(AND(A2="On Web",D2>0,B2="Yes",C2="No"),"Core - Leave on Web","")))))

 

[Deelof]

Do columns B and C need to be Yes/No for the formula to work or can they remain as True/False?
Columns B & C have a look up formula in, will this be preventing the above formula to not
work?

 

[Muhammad_Usman]

Do columns B and C need to be Yes/No for the formula to work or can they remain as True/False?
Columns B & C have a look up formula in, will this be preventing the above formula to not
work?

when i use true and fasle its not working for me so i changed to Yes /No Then it works

 

[kevin9999]

I think the formula provided by @Muhammad_Usman works - if you just change the "Yes" and "No" for TRUE and FALSE (without the quotation marks)

logic.xlsx
	A	B	C	D	E
1	WEB STATUS	CORE	NON CORE	STOCK	ACTION
2	ON WEB	FALSE	FALSE	2	Remove From Web
3	ON WEB	FALSE	FALSE	5	Leave On Web
4	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
5	ON WEB	FALSE	TRUE	1	Non Core - Leave on Web
6	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
7	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
8	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
9	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
10	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
11	ON WEB	FALSE	FALSE	0	Remove From Web
12	ON WEB	FALSE	FALSE	10	Leave On Web
13	NOT ON WEB	FALSE	FALSE	32	Date Checks
14	NOT ON WEB	FALSE	FALSE	20	Date Checks
15	ON WEB	FALSE	FALSE	3	Leave On Web
16	ON WEB	FALSE	FALSE	6	Leave On Web
17	ON WEB	FALSE	FALSE	12	Leave On Web
18	ON WEB	FALSE	FALSE	7	Leave On Web
19	ON WEB	TRUE	FALSE	1	Core - Leave on Web
20	ON WEB	TRUE	FALSE	9	Core - Leave on Web
21	ON WEB	FALSE	TRUE	0	Non Core - Leave on Web
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=IF(AND(A2="On Web",D2<=2,B2=FALSE,C2=FALSE),"Remove From Web",IF(AND(A2="On Web",D2>0,B2=FALSE,C2=FALSE),"Leave On Web",IF(AND(A2="Not On Web",D2>2),"Date Checks",IF(AND(A2="On Web",D2<2,B2=FALSE,C2=TRUE),"Non Core - Leave on Web",IF(AND(A2="On Web",D2>0,B2=TRUE,C2=FALSE),"Core - Leave on Web","")))))

If this does what you want, make sure you mark @Muhammad_Usman 's answer (post #6) as the solution as he did all the hard work on the formula.

 

[bebo021999]

I understand your request as follows:

• If at least 1 TRUE, regardless of stock: Core or Noncore
• else, if stock <= 2: "Remove"
• else, if "NOT ON WEB", "Check", else, "Leave"
• In your explanation, there is no case for ("NOT ON WEB" and stock <= 2), so I assume it is "Remove" (stock <= 2).

Book1
	A	B	C	D	E
1	WEB STATUS	CORE	NON CORE	STOCK
2	ON WEB	FALSE	FALSE	2	Remove from Web
3	ON WEB	FALSE	FALSE	5	Leave on Web
4	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
5	ON WEB	FALSE	TRUE	1	Non Core -Leave on Web
6	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
7	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
8	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
9	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
10	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
11	ON WEB	FALSE	FALSE	0	Remove from Web
12	ON WEB	FALSE	FALSE	10	Leave on Web
13	NOT ON WEB	FALSE	FALSE	32	Date Checks
14	NOT ON WEB	FALSE	FALSE	20	Date Checks
15	ON WEB	FALSE	FALSE	3	Leave on Web
16	ON WEB	FALSE	FALSE	6	Leave on Web
17	ON WEB	FALSE	FALSE	12	Leave on Web
18	ON WEB	FALSE	FALSE	7	Leave on Web
19	ON WEB	TRUE	FALSE	1	Core - Leave on Web
20	ON WEB	TRUE	FALSE	9	Core - Leave on Web
21	ON WEB	FALSE	TRUE	0	Non Core -Leave on Web
Sheet1

Cell Formulas
Range	Formula
E2:E21	E2	=IFERROR(CHOOSE(MATCH(TRUE,B2:C2,0),"Core - ","Non Core -") & "Leave on Web",IF(D2<=2,"Remove from Web",IF(LEFT(A2,3)="NOT","Date Checks","Leave on Web")))