Finding a word in a cell and looking down that column and returning a result based on 2 pieces of criteria - Really stuck!

[matttytn2]

Hello all,

I am hoping someone can help me.

There is a report that shows user files being backed upsuccessfully or not.

In column D is the success status. If a backup fails it tries again up to 3 timesthat same day for each user. The data issorted by username and in date order. My example just has 1 UserName but thiscan be many different people.
In column E I am trying to find out if the backup triedagain and was successful or not; so if cell D contains “failure” I want to lookdown D to find the first instance of the word Success it then needs to check incolumn C if it’s the same day as the failure entry and in column F if it’s the sameusername. If all these conditions aremet return the result “Yes” if not “backup failed on retry”



[TABLE="width: 1139"]
<tbody>[TR]
[TD="width: 64"]Month
[/TD]
[TD="width: 165"]Timestamp
[/TD]
[TD="width: 79"]Day
[/TD]
[TD="width: 151"]BackupSuccessStatus
[/TD]
[TD="width: 877"]SuccessAfterRetry
[/TD]
[TD="width: 183"]UserName
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-10T03:15:18.303Z
[/TD]
[TD]2019-07-10
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-10T11:14:46.527Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-10
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-10T19:15:14.482Z
[/TD]
[TD]2019-07-10
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-11T03:16:13.583Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-11
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-11T11:15:07.287Z
[/TD]
[TD]2019-07-11
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-11T19:16:43.063Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-11
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-12T03:16:43.754Z
[/TD]
[TD]2019-07-12
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-12T11:14:42.834Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-12
[/TD]
[TD="bgcolor: #D9E1F2"]Failure
[/TD]
[TD="bgcolor: #D9E1F2"]if cell in D contains "Failure" look down D to find "success" and if the date and user name are the same as the failure say "yes" if not "no"
[/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-12T19:15:49.091Z
[/TD]
[TD]2019-07-12
[/TD]
[TD]Failure
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-13T03:14:57.752Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-13
[/TD]
[TD="bgcolor: #D9E1F2"]Failure
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-16T11:14:32.349Z
[/TD]
[TD]2019-07-16
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-16T19:14:19.309Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-16
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-17T03:14:49.159Z
[/TD]
[TD]2019-07-17
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-17T11:19:17.870Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-17
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-17T19:14:35.656Z
[/TD]
[TD]2019-07-17
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-18T03:15:00.344Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-18
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-18T11:14:48.402Z
[/TD]
[TD]2019-07-18
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD="bgcolor: #D9E1F2"]Jul-19
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-18T19:16:22.214Z
[/TD]
[TD="bgcolor: #D9E1F2"]2019-07-18
[/TD]
[TD="bgcolor: #D9E1F2"]Success
[/TD]
[TD="bgcolor: #D9E1F2"][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-19T03:16:25.317Z
[/TD]
[TD]2019-07-19
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD]Joe Bloggs
[/TD]
[/TR]
[TR]
[TD]Jul-19
[/TD]
[TD]2019-07-19T11:14:44.400Z
[/TD]
[TD]2019-07-19
[/TD]
[TD]Success
[/TD]
[TD][/TD]
[TD="bgcolor: transparent"]Joe Bloggs
[/TD]
[/TR]
</tbody>[/TABLE]

Sorry I couldn't figure how to attach file?

 

[Fluff]

How about

Book1
	A	B	C	D	E	F
1	Month	Timestamp	Day	BackupSuccessStatus	SuccessAfterRetry	UserName
2	Jul-19	2019-07-10T03:15:18.303Z	10/07/2019	Success		Joe Bloggs
3	Jul-19	2019-07-10T11:14:46.527Z	10/07/2019	Success		Joe Bloggs
4	Jul-19	2019-07-10T19:15:14.482Z	10/07/2019	Success		Joe Bloggs
5	Jul-19	2019-07-11T03:16:13.583Z	11/07/2019	Success		Joe Bloggs
6	Jul-19	2019-07-11T11:15:07.287Z	11/07/2019	Success		Joe Bloggs
7	Jul-19	2019-07-11T19:16:43.063Z	11/07/2019	Success		Joe Bloggs
8	Jul-19	2019-07-12T03:16:43.754Z	12/07/2019	Success		Joe Bloggs
9	Jul-19	2019-07-12T11:14:42.834Z	12/07/2019	Failure	Yes	Joe Bloggs
10	Jul-19	2019-07-12T19:15:49.091Z	12/07/2019	Failure	Yes	Joe Bloggs
11	Jul-19	2019-07-13T03:14:57.752Z	12/07/2019	Success		Joe Bloggs
12	Jul-19	2019-07-16T11:14:32.349Z	16/07/2019	Failure	No	Joe Bloggs
13	Jul-19	2019-07-16T19:14:19.309Z	16/07/2019	Failure	No	Joe Bloggs
14	Jul-19	2019-07-17T03:14:49.159Z	17/07/2019	Success		Joe Bloggs
15	Jul-19	2019-07-17T11:19:17.870Z	17/07/2019	Success		Joe Bloggs
16	Jul-19	2019-07-17T19:14:35.656Z	17/07/2019	Success		Joe Bloggs
17	Jul-19	2019-07-18T03:15:00.344Z	18/07/2019	Success		Joe Bloggs
18	Jul-19	2019-07-18T11:14:48.402Z	18/07/2019	Success		Joe Bloggs
19	Jul-19	2019-07-18T19:16:22.214Z	18/07/2019	Success		Joe Bloggs
20	Jul-19	2019-07-19T03:16:25.317Z	19/07/2019	Success		Joe Bloggs
21	Jul-19	2019-07-19T11:14:44.400Z	19/07/2019	Success		Joe Bloggs
Master Image

Cell Formulas
Range	Formula
E2	{=IF(D2<>"Failure","",IF(ISNUMBER(MATCH("Success"&C2&F2,D3:D$21&C3:C$21&F3:F$21,0)),"Yes","No"))}
Press CTRL+SHIFT+ENTER to enter array formulas.