nested IF/AND/OR/Contains

[nikhil0311]

Book1
	A	B	C	D	E	F
1	Risk Raiting	Country	Renewal date	Output		Relevant countries
2	Low	Canada;Germany;UK	Friday, October 26, 2018	Yes		UK
3	High	Canada;Luxembourg;UK	Saturday, October 27, 2018	No		United Kingdom
4	Medium	AU;EMEA;NZ	Friday, October 19, 2018	No		EMEA
5	Low	Luxembourg	Friday, October 27, 2017	Yes		BE
6	Medium	Aus	Saturday, October 20, 2018	No		Luxembourg
7						Spain
8						IE
Sheet1

data is available from A1 to C6 and final output is required in column D i.e. output. If all the below 3 conditions are met then column D should have Yes or No
List of Relevant countries are listed in column F2 to F8. if any of the listed country is present then 2nd condition i.e. Country Column B should be true




if Risk raiting i.e. column A is Low OR Medium


AND


If Country i.e. colum B contains (Any country listed from column F1 to F8)

AND


If Renewal date i.e. Column C is greater than today's date ( todays date is 23 oct 2018, so for tomorrow todays date will be 24th Oct 2018)

if all of the above 3 conditions are satisfied then Column D2 should be Yes or No
you can find the final output listed in column D2

thanks in advance

 

[jtakw]

Re: nested IF/AND/OR/Contains help required

Hi,

A couple of questions:
1. Are those Real Date values in Column C?
2. When you say "greater than today's date", you mean excluding "equal" to today, is that correct?

 

[jtakw]

Re: nested IF/AND/OR/Contains help required

Well, assuming those a Real Date Values in Column C, and "greater than today" does Not include today.

Formula in D2 copied down:

Book1
	A	B	C	D	E	F
1	Risk Raiting	Country	Renewal date	Output		Relevant countries
2	Low	Canada;Germany;UK	Friday, October 26, 2018	Yes		UK
3	High	Canada;Luxembourg;UK	Saturday, October 27, 2018	No		United Kingdom
4	Medium	AU;EMEA;NZ	Friday, October 19, 2018	No		EMEA
5	Low	Luxembourg	Saturday, October 27, 2018	Yes		BE
6	Medium	Aus	Saturday, October 20, 2018	No		Luxembourg
7						Spain
8						IE
Sheet343

Cell Formulas
Range	Formula
D2	=IF(OR(A2="High",C2<=TODAY()),"No",IF(ISNUMBER(LOOKUP(2,1/SEARCH(F$2:F$8,B2))),"Yes","No"))

 

[nikhil0311]

Re: nested IF/AND/OR/Contains help required

this is awesome. I have another request , could you please provide me a solution for the below?

Book1
	A	B	C	D	E	F	G
1	Output	Migration Date	Renewal Date	5 month prior date (migration dt column B -(substract) 5 months prior)	Risk	Flag	Final Comment
2	Yes	Sunday, March 1, 2020	Friday, July 5, 2019	Tuesday, October 1, 2019	Low	PEP	Ready to Migrate
3	No	Tuesday, February 12, 2019	Tuesday, January 1, 2019	Wednesday, September 12, 2018	High	No	2 update to UK standard required at next schedule renewal
4	No	Monday, June 18, 2018	Sunday, January 1, 2017	Thursday, January 18, 2018	High	No	3 update to UK standard required - remedation
5	No	Thursday, November 14, 2019	Wednesday, June 3, 2020	Friday, June 14, 2019	High	No	4 update to target location scheduled renewal
6	No	Wednesday, June 3, 2020	Monday, June 18, 2018	Friday, June 14, 2019	Low	PEP	5 update to target location - Remediation
Sheet2

Raw data is available from Column A to C & E to F
I need to calculate column D i.e. (column B Migration date -(substract) 5 months prior) and then based on below conditions final comments in Column G


If Output i.e. column A is Yes then Final comment i.e. Column G should be "Ready to Migrate"
If Output i.e. column A is No AND Column D date is less than Renewal date i.e. column C then Final comment i.e. Column G should be "2 update to UK standard required at next schedule renewal"
If Output i.e. column A is No AND Column D date is Greater than Renewal date i.e. column C then Final comment i.e. Column G should be "3 update to UK standard required - remedation"
IF column E i.e. Risk is High OR Column F i.e. Flag = PEP AND Column D date is less than Renewal date i.e. column C then final comment i.e. column G should be "4 update to target location scheduled renewal"
IF column E i.e. Risk is High OR Column F i.e. Flag = PEP AND Column D date is Greater than Renewal date i.e. column C then final comment i.e. column G should be "5 update to target location - Remediation"

let me know if you have any questions