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”

Month	Timestamp	Day	BackupSuccessStatus	SuccessAfterRetry	UserName
Jul-19	2019-07-10T03:15:18.303Z	2019-07-10	Success		Joe Bloggs
Jul-19	2019-07-10T11:14:46.527Z	2019-07-10	Success		Joe Bloggs
Jul-19	2019-07-10T19:15:14.482Z	2019-07-10	Success		Joe Bloggs
Jul-19	2019-07-11T03:16:13.583Z	2019-07-11	Success		Joe Bloggs
Jul-19	2019-07-11T11:15:07.287Z	2019-07-11	Success		Joe Bloggs
Jul-19	2019-07-11T19:16:43.063Z	2019-07-11	Success		Joe Bloggs
Jul-19	2019-07-12T03:16:43.754Z	2019-07-12	Success		Joe Bloggs
Jul-19	2019-07-12T11:14:42.834Z	2019-07-12	Failure	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"	Joe Bloggs
Jul-19	2019-07-12T19:15:49.091Z	2019-07-12	Failure		Joe Bloggs
Jul-19	2019-07-13T03:14:57.752Z	2019-07-13	Failure		Joe Bloggs
Jul-19	2019-07-16T11:14:32.349Z	2019-07-16	Success		Joe Bloggs
Jul-19	2019-07-16T19:14:19.309Z	2019-07-16	Success		Joe Bloggs
Jul-19	2019-07-17T03:14:49.159Z	2019-07-17	Success		Joe Bloggs
Jul-19	2019-07-17T11:19:17.870Z	2019-07-17	Success		Joe Bloggs
Jul-19	2019-07-17T19:14:35.656Z	2019-07-17	Success		Joe Bloggs
Jul-19	2019-07-18T03:15:00.344Z	2019-07-18	Success		Joe Bloggs
Jul-19	2019-07-18T11:14:48.402Z	2019-07-18	Success		Joe Bloggs
Jul-19	2019-07-18T19:16:22.214Z	2019-07-18	Success		Joe Bloggs
Jul-19	2019-07-19T03:16:25.317Z	2019-07-19	Success		Joe Bloggs
Jul-19	2019-07-19T11:14:44.400Z	2019-07-19	Success		Joe Bloggs

<tbody>
</tbody>

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.