List all matching values between two named ranges based on criteria

[mehidy1437]

Hi, I have got this here , now I need to get the list of matching value at cell J3 to downwards.
How should I alter this formula to get the matching values?
I'd be grateful if anyone could help with this?

Book1
	A	B	C	D	E	F	G	H	I	J
1	evaluated_name			executives	exit_date				NOT MATCH	MATCH
2
3	aa			aa	on duty				kk
4	bb			kk	on duty				gg
5	cc			cc	on duty				jj
6	dd			dd	on duty
7	ee			gg	on duty
8	ff			hh	out duty
9				jj	on duty
Sheet3

Cell Formulas
Range	Formula
I3:I9	I3	=IFERROR(INDEX(executives,MATCH(1,(COUNTIF($I$2:I2,executives)=0)*(exit_date="On Duty")*(ISERROR(MATCH(executives,evaluated_name,0))),0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas.

Named Ranges
Name	Refers To	Cells
evaluated_name	=Sheet3!$A$3:$A$8	I3:I9
executives	=Sheet3!$D$3:$D$9	I3:I9
exit_date	=Sheet3!$E$3:$E$9	I3:I9

 

[Fluff]

How about

=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$8,$D$3:$D$9,0)))/($E$3:$E$9="On Duty"),ROWS(J$3:J3))),"")

 

[mehidy1437]

Many thanks, it's worked.
Could you please alter the main formula to get the matching values?

 

[Fluff]

That does get the matching values.

 

[mehidy1437]

How about

=IFERROR(INDEX($A$3:$A$8,AGGREGATE(15,6,(ROW($A$3:$A$8)-ROW($A$3)+1)/(ISNUMBER(MATCH($A$3:$A$8,$D$3:$D$9,0)))/($E$3:$E$9="On Duty"),ROWS(J$3:J3))),"")

I also want to get the non-matching data list. so I want to use a similar formula for that.
So, give me the alternative formula with INDEX-AGGREGATE-MATCH to get the non-matching value.

 

[Fluff]

Try

=IFERROR(INDEX($D$3:$D$9,AGGREGATE(15,6,(ROW($D$3:$D$9)-ROW($D$3)+1)/(ISNA(MATCH($D$3:$D$9,$A$3:$A$8,0)))/($E$3:$E$9="On Duty"),ROWS(J$3:J3))),"")

 

[mehidy1437]

I will check in morning, thanks for your help.

 

[mehidy1437]

Perfect, thanks a lot.

 

[Fluff]

Glad to help & thanks for the feedback.

 

[mehidy1437]

@Fluff , I have tried to figure out, how to get the not matching list of values from both columns A & D, in a single column. But I couldn't able to do that.

I have got a solution based on CTR+SHIFT+ENTER, but I would like to know If it's possible to get the list using INDEX & AGGREGATE?