Searching through multiple columns for matches then return first date from another column if true

[AmyH_NHSBI]

Hi all
I have had this problem before but found a work around in SQL, unfortunately it won't work for this particular piece of work.

I need a formula that will look through Col E to see if there is a duplicate, if so, I want it to then look at Col G to see if the dates are the same, if the dates are the same I would then want it to look at Col H and return the first chronological date. If the conditions are not true, so no dupes in Col E to start with, it would just return the date in Col H.

I hope this makes sense? I have pasted a sample of data with sensitive info removed.

Thank you!

A	B	C	D	E	F	G	H
1​	A	Condition 1	C111	A-C111	HT	29/01/2020​	13/02/2020​
2​	B	Condition 2	C222	B-C222	AM	24/01/2019​	04/02/2019​
3​	C	Condition 3	C333	C-C333	C	05/12/2019​	30/12/2019​
4​	D	Condition 4	C444	D-C444	S	24/09/2019​	04/11/2019​
5​	E	Condition 5	C555	E-C555	C	25/03/2020​	19/06/2020​
6​	F	Condition 6	C666	F-C666	C	28/01/2020​	11/02/2020​
7​	G	Condition 7	C777	G-C777	S	12/09/2019​	01/10/2019​
7​	G	Condition 7	C777	G-C777	S	12/09/2019​	15/10/2019​
7​	G	Condition 7	C777	G-C777	HT	12/09/2019​	30/10/2019​
8​	H	Condition 8	C888	H-C888	HT	25/10/2019​	25/10/2019​
8​	H	Condition 8	C888	H-C888	C	25/10/2019​	26/11/2019​
9​	I	Condition 9	C999	I-C999	C	31/10/2019​	03/12/2019​
10​	J	Condition 10	C100	J-C100	S	11/07/2019​	11/07/2019​
11​	K	Condition 11	C110	K-C110	S	08/02/2019​	18/03/2019​
12​	L	Condition 12	C120	L-C120	C	11/02/2019​	26/03/2019​
12​	L	Condition 12	C120	L-C120	AM	11/02/2019​	22/01/2020​
12​	L	Condition 12	C120	L-C120	C	11/02/2019​	03/06/2020​
13​	M	Condition 13	C130	M-C130	C	07/01/2020​	05/02/2020​

 

[Fluff]

Hi & welcome to MrExcel.
How about

+Fluff New.xlsm
	A	B	C	D	E	F	G	H	I
1	A	B	C	D	E	F	G	H
2	1	A	Condition 1	C111	A-C111	HT	29/01/2020	13/02/2020	13/02/2020
3	2	B	Condition 2	C222	B-C222	AM	24/01/2019	02/04/2019	02/04/2019
4	3	C	Condition 3	C333	C-C333	C	12/05/2019	30/12/2019	30/12/2019
5	4	D	Condition 4	C444	D-C444	S	24/09/2019	11/04/2019	11/04/2019
6	5	E	Condition 5	C555	E-C555	C	25/03/2020	19/06/2020	19/06/2020
7	6	F	Condition 6	C666	F-C666	C	28/01/2020	02/11/2020	02/11/2020
8	7	G	Condition 7	C777	G-C777	S	09/12/2019	10/01/2019	10/01/2019
9	7	G	Condition 7	C777	G-C777	S	09/12/2019	15/10/2019	10/01/2019
10	7	G	Condition 7	C777	G-C777	HT	09/12/2019	30/10/2019	10/01/2019
11	8	H	Condition 8	C888	H-C888	HT	25/10/2019	25/10/2019	25/10/2019
12	8	H	Condition 8	C888	H-C888	C	25/10/2019	26/11/2019	25/10/2019
13	9	I	Condition 9	C999	I-C999	C	31/10/2019	12/03/2019	12/03/2019
14	10	J	Condition 10	C100	J-C100	S	07/11/2019	07/11/2019	07/11/2019
15	11	K	Condition 11	C110	K-C110	S	02/08/2019	18/03/2019	18/03/2019
16	12	L	Condition 12	C120	L-C120	C	02/11/2019	26/03/2019	26/03/2019
17	12	L	Condition 12	C120	L-C120	AM	02/11/2019	22/01/2020	26/03/2019
18	12	L	Condition 12	C120	L-C120	C	02/11/2019	06/03/2020	26/03/2019
19	13	M	Condition 13	C130	M-C130	C	01/07/2020	02/05/2020	02/05/2020
Master

Cell Formulas
Range	Formula
I2:I19	I2	=AGGREGATE(15,6,$H$2:$H$19/($E$2:$E$19=E2)/($G$2:$G$19=G2),1)