Google sheet formula: TRUE if column B is duplicate in either column A or column B

Question

I have two columns, Name 1 and Name 2. I need an ARRAYFORMULA formula that tells me whether Name 2 appears elsewhere in either Name 1 or Name 2.

Name 1	Name 2	Is Name2 duplicate (formula)
Sam	Jay
Sandra	Judy	Yes
Jane	Judy	Yes
Pete	Sam	Yes
Pete	Sarah
Gerry	Gerry	Yes

It needs to be an arrayformula so it gets added automatically to any new rows.

I can compare either column to a single column:

={"match name 1";ARRAYFORMULA(if(COUNTIF (A:A, B2:B)>0, "yes",""))}
={"match name 2";ARRAYFORMULA(if(COUNTIF (B:B, B2:B)>1, "yes",""))}

I can also make a non-arrayformula formula that compares to both columns:

=OR(COUNTIF (A:A, B2:B)>0,COUNTIF (B:B, B2:B)>1)

However, I can't seem to get the OR() and COUNTIF to work with ARRAYFORMULA.

Answer 1

Modifying your suggested formula to be:

={"match name 2";arrayformula(if(len(B2:B),if(countif(A:B,B2:B)>1,"Yes",""),))}

Alternative:

=map(B2:B,lambda(Σ,if(Σ="",,if(countif(A:B,Σ)>1,"Yes",))))