Excel Formula - Return Unique Value based on 2 Criterias

[hrayani]

Hello Friends,

Please have a look at the sample data... I need to add another criteria that if the date column is blank then do not consider it

TEST FILE.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Customer	Supplier	DATE
2	1	Humayun	01-Jan
3	1	Humayun	01-Jan
4	1	Charlie
5	1	John	01-Jan
6	5	B	01-Jan
7	6	Charlie
8	1	Charlie
9	1	Humayun	01-Jan
10
11	Customer	Suppliers
12	1	Humayun
13		Charlie	This name should not be in the list as the adjacent column does not have a date in it
14		John
Sheet5

Cell Formulas
Range	Formula
B12:B14	B12	=IFERROR(INDEX($B$2:$B$9, MATCH(0, IF($A$12=$A$2:$A$9, COUNTIF($B$11:$B11, $B$2:$B$9), ""), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Fluff]

How about

+Fluff 1.xlsm
	A	B	C
1	Customer	Supplier	DATE
2	1	Humayun	01/01/2021
3	1	Humayun	01/01/2021
4	1	Charlie
5	1	John	01/01/2021
6	5	B	01/01/2021
7	6	Charlie
8	1	Charlie
9	1	Humayun	01/01/2021
10
11	Customer	Suppliers
12	1	Humayun
13		John
14
15
Main

Cell Formulas
Range	Formula
B12:B14	B12	=IFERROR(INDEX($B$2:$B$9,AGGREGATE(15,6,(ROW($B$2:$B$9)-ROW($B$2)+1)/($A$2:$A$9=$A$12)/($C$2:$C$9<>"")/(ISNA(MATCH($B$2:$B$9,B$11:B11,0))),1)),"")

 

[hrayani]

Thanks Fluff,
Works like a Charm

 

[Fluff]

You're welcome & thanks for the feedback.