Search a range for a match within an array; return "Y" if cell value match is found?

[vgoodbldg]

I have two pages, each with 2 columns of addresses per row.

I am trying to search for duplicate address values across columns on each sheet. I need to return "Y" in a third column on page 2 if one or both columns on page 1 contain a value from page 2's respective row. The value in A4 on page 1 might be in B100 on page 2, and vice versa.

Can I do this without a VBA?

Thank you!

 

[etaf]

a countif() may work, or countifs()
But just to be clear
sheet 2 has

Book4
	A	B
2	5	a
3	2	b
4	1	c
Sheet2

And Sheet 1 has

Book4
	A	B
2	4	a
3	5	e
4	1	w
Sheet1

so would the highlight cells match
or do they both need to be on the same row

 

[etaf]

anyway - heres a formula for if either match

Book4
	A	B	C
2	5	a	Y
3	2	b
4	1	c	Y
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=IF(COUNTIF(Sheet1!A2:A4,Sheet2!A2)+COUNTIF(Sheet1!B2:B4,Sheet2!B2)>0,"Y","")

Book4
	A	B
2	4	a
3	5	e
4	1	w
Sheet1

 

[vgoodbldg]

anyway - heres a formula for if either match

Book4
	A	B	C
2	5	a	Y
3	2	b
4	1	c	Y
Sheet2

Cell Formulas
Range	Formula
C2:C4	C2	=IF(COUNTIF(Sheet1!A2:A4,Sheet2!A2)+COUNTIF(Sheet1!B2:B4,Sheet2!B2)>0,"Y","")

Book4
	A	B
2	4	a
3	5	e
4	1	w
Sheet1

Thank you! This works for about 75% of the workbook, but the problem is when columns are referencing the other columns.

A can be in column 1 or 2, and B can be in column 1 or 2. As long as the value of A or B is a match the array, then Y.

I tried adding an additional two countifs, but failed:

=IF(COUNTIF(Sheet1!A2:A4,Sheet2!A2)+COUNTIF(Sheet1!A2:A4,Sheet2!B2)+COUNTIF(Sheet1!B2:B4,Sheet2!B2)+COUNTIF(Sheet1!B2:B4,Sheet2!A2)>0,"Y","")

 

[etaf]

you can just extend the range
=IF((COUNTIF(Sheet1!A2:B4,Sheet2!A2)+COUNTIF(Sheet1!A2:AB4,Sheet2!B2))>0,"Y","N")
so you are looking for the value in Cell A2 to be anywhere in the columns A OR B - OR - if the value in Cell B2 to be anywhere in the columns A and B

Add $ to fix the range
=IF((COUNTIF(Sheet1!$A$2:$B$4,Sheet2!A2)+COUNTIF(Sheet1!$A$2:$A$B4,Sheet2!B2))>0,"Y","N")

 

[vgoodbldg]

you can just extend the range
=IF((COUNTIF(Sheet1!A2:B4,Sheet2!A2)+COUNTIF(Sheet1!A2:AB4,Sheet2!B2))>0,"Y","N")
so you are looking for the value in Cell A2 to be anywhere in the columns A OR B - OR - if the value in Cell B2 to be anywhere in the columns A and B

Add $ to fix the range
=IF((COUNTIF(Sheet1!$A$2:$B$4,Sheet2!A2)+COUNTIF(Sheet1!$A$2:$A$B4,Sheet2!B2))>0,"Y","N")

Perfection. Thank you kind stranger.

 

[etaf]

you are welcome