What formula can I use?

[fafo1182]

If I have two sheets with lists of names and want to create a third sheet with a list of duplicate names on the first two sheets. Is there a way to do it?

 

[awoohaw]

I'm sure there is probably a shorter formula that gets your desired result. But, this is what I've come up with:

Book1
	A	B	C	D	E
1	Sheet1		Sheet2		more than once
2	Name1		Name3		Name3
3	Name2		Name4		Name4
4	Name3		Name5
5	Name4		Name6
Sheet1

Cell Formulas
Range	Formula
E2:E3	E2	=LET(a,A2:A5,b,C2:C5,c,VSTACK(a,b),d,UNIQUE(c,,TRUE),e,UNIQUE(c,,FALSE), f,--(TRANSPOSE(d)=e),g,(--(BYROW(f,LAMBDA(f,SUM(f)))=0)),FILTER(e,g,""))
Dynamic array formulas.

 

[StephenCrump]

I'm sure there is probably a shorter formula ...

=FILTER(A2:A5,ISNUMBER(MATCH(A2:A5,C2:C5,)),"n/a")

(I've interpreted the question the same way, i.e. "duplicate names" means names appearing on Sheet 1 and Sheet 2)

 

[Peter_SSs]

@fafo1182
Welcome to the MrExcel board!

(I've interpreted the question the same way, i.e. "duplicate names" means names appearing on Sheet 1 and Sheet 2)

Me too.
=FILTER(A2:A5,COUNTIF(C2:C5,A2:A5),"")