Multiple if statements based on dates

[StillUnderstanding]

Hello,

I wonder if someone might be able to helps me with an IF statement. I am looking to do the following:-

If the time between 2 dates is less than 14 then return the word "Yes", If the time between 2 dates is more than 14.01 then return the word "No", If the time is less than 14 from today then return "Not Included"

Having some issues trying to work this out and hope someone might be able to help?

 

[Sufiyan97]

Thanks for that

Try

=IF(ISNUMBER(MATCH(AN2,Sheet2!AM:AM,0)),IF(SUM(TODAY()-Sheet2!A4)<14,"Under 14 Days from Today",IF(SUM(B4-Sheet2!A4)>14,"More than 14 Days",IF(SUM(B4-Sheet2!A4)<14,"less than 14 days from column A in sheet 2"))),"Email Not Found in Sheet2")

 

[Sufiyan97]

Sorry ignore that and Try

=IFNA(IF(SUM(TODAY()-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))<14,"Under 14 Days from Today",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))>14,"More than 14 Days",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2")

 

[StillUnderstanding]

Sorry ignore that and Try

=IFNA(IF(SUM(TODAY()-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))<14,"Under 14 Days from Today",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))>14,"More than 14 Days",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!AM:AM,0)))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2")

Thanks @Sufiyan97 this works!

One last question please.

If Sheet 2 has a blank cell or #N/A in the cell (Line 9 for example) like the below:-

Then in Sheet 1 how would I get the code to return the words "No Date" (Line 4 for example)

Thanks again for your help with this, I really appreciate it.

 

[Sufiyan97]

Try

=IF(OR(ISERROR(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0))),ISBLANK(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))),"No Date",IFNA(IF(SUM(TODAY()-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))<14,"Under 14 Days from Today",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))>14,"More than 14 Days",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))

 

[StillUnderstanding]

Yes this is great! Thank you so much!

I have encountered an slight issue with the code when it comes to having the same email listed on the sheet twice.

In the below from Sheet 2 highlighting the issue in green:-

How can I get the formula to match the email in "AM" and then check column "B" and if it has the words "Delayed" or "Left" in the cell, if they do appear then they would ignore that match and keep searching until it finds a match with words its told not to ignore?

Try

=IF(OR(ISERROR(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0))),ISBLANK(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))),"No Date",IFNA(IF(SUM(TODAY()-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))<14,"Under 14 Days from Today",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))>14,"More than 14 Days",IF(SUM(B4-INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))[/COD
[/QUOTE]

 

[Sufiyan97]

Try

=IF(OR(ISERROR(INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2)))),ISBLANK(INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))<14,"Under 14 Days from Today",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))>14,"More than 14 Days",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))

 

[StillUnderstanding]

Try

=IF(OR(ISERROR(INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2)))),ISBLANK(INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))<14,"Under 14 Days from Today",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))>14,"More than 14 Days",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AP2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AP$2:AP2,AP2))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))

Thanks for this and your amazing work, its far beyond my skill level.

It is not quite right as I think the dates it's looking at are the wrong way round.

On Sheet 2 for Date 1, this will always have a date, the variable is on Sheet 1 as it may not have a date.

Sheet 1 looks like this

Sheet 2 looks like this:-

I would be looking for the code to sit in column "D" of Sheet 1 and for it to check Sheet 2 for a matching email address and the group in "B" and then compare the date to tell me if it fits in with one of the following:-

• Under 14 Days from Today
• More than 14 Days
• Less than 14 days from column A in sheet 2
• Missing Email
• Date 2 is earlier than date 1

It feels like its almost perfect. Are you able to help me with this please?

Thank you

 

[Sufiyan97]

Try and let me know if it works

Index AGGREGATE.xlsx
	A	B	C	D																																				AN	AO
1		Date	Today	Is the date less than 14 days?																																				Email Address
2		11/5/2022	7/1/2022	No Date																																				me3@me.com
3		19/05/2022	7/1/2022	No Date																																				me5@me.com
4		18/05/2022	7/1/2022	Under 14 Days from Today																																				me1@me.com
5
6
7
8
9
10
Sheet1

Cell Formulas
Range	Formula
D2:D4	D2	=IF(OR(ISERROR(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0))),ISBLANK(INDEX(Sheet2!$A$1:$A$500,MATCH(AN2,Sheet2!$AM$1:$AM$500,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AN2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AN$2:AN2,AN2))))<14,"Under 14 Days from Today",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AN2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AN$2:AN2,AN2))))>14,"More than 14 Days",IF(SUM(B2-INDEX(Sheet2!$A$2:$A$500,AGGREGATE(15,6,(ROW(Sheet2!$AM$2:$AM$500)-ROW(Sheet2!$AM$2)+1)/(Sheet2!$AM$2:$AM$500=AN2)/(Sheet2!$B$2:$B$500<>"Delayed")/(Sheet2!$B$2:$B$500<>"left"),COUNTIF($AN$2:AN2,AN2))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))

 

[StillUnderstanding]

Thanks @Sufiyan97, this really helps.

I have added the 2 sheets together and used XL2BB as I think it might help?

Would you be able to help me overcome the below last bits? I would be very grateful.

When "Not Complete" in "C" it should say "Not Complete" in "E"

When no matching email in "Cc" it should say "Email Not Found in Sheet2" in "E"

When "Blank" in "C" it should say "No March" in "E"

If the date in "C" is earlier than the date in "AQ" then it should say "Completed before Start Date" in "E"

Data.xlsx
	A	B	C	D	E																																				AO	AP	AQ	AR																																					CC	CD
1	Comment		Data from Sheet 1																																								Data From Sheet 2
2			Date Completed	Today																																					Email Address		Start Date	Group																																					Email Address
3			01/06/2022		less than 14 days from column A in sheet 2																																				me1@me.com		01/06/2022	U																																					me1@me.com
4			15/06/2022		More than 14 Days																																				me2@me.com		15/05/2022	D																																					me2@me.com
5	When "Not Complete" in "C" it should say "Not Complete" in "E"		Not Completed		Email not found in Sheet2																																				me3@me.com		01/06/2022	U																																					me3@me.com
6	When no matching email in "Cc" it should say "Email Not Found in Sheet2" in "E"		12/06/2022		No Date																																				me4@me.com		12/06/2022	B																																					me9@me.com
7	When "Blank" in "C" it should say "No March" in "E"		Blank		Email not found in Sheet2																																				me5@me.com		11/05/2022	A																																					me5@me.com
8			07/07/2021		Email not found in Sheet2																																				me6@me.com		01/06/2022	Delayed																																					me6@me.com
9			04/09/2021		less than 14 days from column A in sheet 2																																				me7@me.com		04/09/2021	A																																					me7@me.com
10	If the date in "C" is earlier than the date in "AQ" then it should say "Completed before Start Date" in "E"		04/04/2022		less than 14 days from column A in sheet 2																																				me8@me.com		04/05/2022	U																																					me8@me.com
11
Sheet1

Cell Formulas
Range	Formula
E3	E3	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO3,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO3,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))<14,"Under 14 Days from Today",IF(SUM(C3-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))>14,"More than 14 Days",IF(SUM(C3-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E4	E4	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO4,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO4,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO4)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$4:AO4,AO4))))<14,"Under 14 Days from Today",IF(SUM(C4-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO4)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$4:AO4,AO4))))>14,"More than 14 Days",IF(SUM(C4-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO4)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$4:AO4,AO4))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E5	E5	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO5,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO5,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO5)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$5:AO5,AO5))))<14,"Under 14 Days from Today",IF(SUM(C5-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO5)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$5:AO5,AO5))))>14,"More than 14 Days",IF(SUM(C5-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO5)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$5:AO5,AO5))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E6	E6	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO6,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO6,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO6)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$6:AO6,AO6))))<14,"Under 14 Days from Today",IF(SUM(C6-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO6)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$6:AO6,AO6))))>14,"More than 14 Days",IF(SUM(C6-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO6)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$6:AO6,AO6))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E7	E7	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO7,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO7,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO7)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$7:AO7,AO7))))<14,"Under 14 Days from Today",IF(SUM(C7-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO7)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$7:AO7,AO7))))>14,"More than 14 Days",IF(SUM(C7-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO7)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$7:AO7,AO7))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E8	E8	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO8,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO8,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO8)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$8:AO8,AO8))))<14,"Under 14 Days from Today",IF(SUM(C8-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO8)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$8:AO8,AO8))))>14,"More than 14 Days",IF(SUM(C8-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO8)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$8:AO8,AO8))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E9	E9	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO9,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO9,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO9)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$9:AO9,AO9))))<14,"Under 14 Days from Today",IF(SUM(C9-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO9)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$9:AO9,AO9))))>14,"More than 14 Days",IF(SUM(C9-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO9)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$9:AO9,AO9))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))
E10	E10	=IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO10,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO10,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO10)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$10:AO10,AO10))))<14,"Under 14 Days from Today",IF(SUM(C10-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO10)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$10:AO10,AO10))))>14,"More than 14 Days",IF(SUM(C10-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO10)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$10:AO10,AO10))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))

 

[Sufiyan97]

Try and let me know if it works:

Book3
	A	B	C	D	E																																				AO	AP	AQ	AR																																					CC	CD	CE	CF
1	Comment		Data from Sheet 1																																								Data From Sheet 2
2			Date Completed	Today																																					Email Address		Start Date	Group																																					Email Address
3			6/1/2022		less than 14 days from column A in sheet 2																																				me1@me.com		6/1/2022	U																																					me1@me.com
4			6/15/2022		More than 14 Days																																				me2@me.com		5/15/2022	D																																					me2@me.com
5	When "Not Complete" in "C" it should say "Not Complete" in "E"		Not Completed		Not Completed																																				me3@me.com		6/1/2022	U																																					me3@me.com
6	When no matching email in "Cc" it should say "Email Not Found in Sheet2" in "E"		6/12/2022		Delayed or Left or email not found																																				me4@me.com		6/12/2022	B																																					me9@me.com
7	When "Blank" in "C" it should say "No March" in "E"		Blank		No Match																																				me5@me.com		5/11/2022	A																																					me5@me.com
8			7/7/2021		Delayed or Left or email not found																																				me6@me.com		6/1/2022	Delayed																																					me6@me.com
9			9/4/2021		less than 14 days from column A in sheet 2																																				me7@me.com		9/4/2021	A																																					me7@me.com
10	If the date in "C" is earlier than the date in "AQ" then it should say "Completed before Start Date" in "E"		4/4/2022		Completed Before Start Date																																				me8@me.com		5/4/2022	U																																					me8@me.com
11
Sheet1

Cell Formulas
Range	Formula
E3:E10	E3	=IF(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3)))>C3),"Delayed or Left or email not found",IF(INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3)))>C3,"Completed Before Start Date",IF(C3="Blank","No Match",IF(C3="Not Completed","Not Completed",IF(OR(ISERROR(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO3,Sheet1!$CC$3:$CC$502,0))),ISBLANK(INDEX(Sheet1!$AQ$3:$AQ$502,MATCH(AO3,Sheet1!$CC$3:$CC$502,0)))),"No Date",IFERROR(IF(SUM(TODAY()-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))<14,"Under 14 Days from Today",IF(SUM(C3-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))>14,"More than 14 Days",IF(SUM(C3-INDEX(Sheet1!$AQ$3:$AQ$501,AGGREGATE(15,6,(ROW(Sheet1!$CC$3:$CC$501)-ROW(Sheet1!$CC$3)+1)/(Sheet1!$CC$3:$CC$501=AO3)/(Sheet1!$AR$3:$AR$501<>"Delayed")/(Sheet1!$AR$3:$AR$501<>"left"),COUNTIF($AO$3:AO3,AO3))))<14,"less than 14 days from column A in sheet 2"))),"Email not found in Sheet2"))))))