Excel Formula - Index Match or Something Else ??

[hrayani]

Dear Friends,

I have the below data from A2:C13
I want to generate a list of Ref # where Target Date <> Blank & Actual Date = Blank
If Target Date <> Blank & Actual date = Blank against all Ref # only then consider it - other wise if against any one Ref # Actual Date <> Blank then don't consider it

Short Formula.xlsx
	A	B	C
1	REF #	Target Date	Actual Date
2	503
3	503
4	504	1-Jul-24
5	504	1-Jul-24	15-Jul-24
6	504	1-Jul-24
7	505	2-Jul-24
8	505	2-Jul-24
9	505	2-Jul-24
10	506	15-May-24
11	507	10-Feb-24
12	507	10-Feb-24
13	507	10-Feb-24
14	507	10-Feb-24
15	507	10-Feb-24	10-Feb-24
16
17
18			Desired Result ↓
19			REF #
20			505
21			506
Sheet2

Regards,

Humayun

 

[kvsrinivasamurthy]

What about 504.

 

[hrayani]

What about 504.

504 Should not be considered because against 1 out of 3 504, actual date is there

 

[kvsrinivasamurthy]

Try.

=IFERROR(INDEX(A$2:A$15,AGGREGATE(15,6,ROW($A$2:$A$15)/(($B$2:$B$15<>"")*($C$2:$C$15="")*ISERROR(MATCH($A$2:$A$15,$G$1:$G1,0))*(COUNTIFS($A$2:$A$15,$A$2:$A$15,$B$2:$B$15,"<>",$C$2:$C$15,"<>")=0)),1)-ROW($A$1)),"")

Copy downwards till blank result is obtained.
If Target Date and Actual Date is required copy across for 3 columns.

 

[hrayani]

Try.

=IFERROR(INDEX(A$2:A$15,AGGREGATE(15,6,ROW($A$2:$A$15)/(($B$2:$B$15<>"")*($C$2:$C$15="")*ISERROR(MATCH($A$2:$A$15,$G$1:$G1,0))*(COUNTIFS($A$2:$A$15,$A$2:$A$15,$B$2:$B$15,"<>",$C$2:$C$15,"<>")=0)),1)-ROW($A$1)),"")

Copy downwards till blank result is obtained.
If Target Date and Actual Date is required copy across for 3 columns.

Many Thanks! for providing the Formula