Lookup Email Addresses Across 4 columns

[Tashat]

Hi All

Hoping someone can help me. I have two sheets, one with legacy contact email addresses and a new sheet where I want to enter new email addresses for contacts. On each sheet there is the option to add two emails, if a person has more than one email address. On sheet 2 where I will enter new contacts, I want a check column (f:f) to alert the user if the email address(es) entered on sheet 2 already appear on the legacy contacts on sheet 1. I therefore have 4 columns across two sheets to compare. So if the email address appears in either of the two columns in any order then it will return "YES" as per my example below. Can someone help me with a formula to perform this action? I'm assuming this would be some mixture of INDEX, MATCH, VLOOKUP? Thank you in advance.

Fruit Example.xlsm
	A	B	C	D	E	F	G
1	Sheet 1 - Legacy Emails			Sheet 2 - New Emails
2	Email 1	Email 2		Email 1	Email 2	Check Email
3	123@123.com	456@123.com		amy@company.com	amy2@company.com	NO
4	abc@abc.com	def@abc.com		def@abc.com		YES
5	test@test.com			456@123.com	123@123.com	YES
6	john@smith.co.uk	john2@smith.com		fred@flinstone.com		NO
7				test@test.com		YES
8				wilma@flinstone.com		NO
9				john2@smith.com		YES
10
11
Sheet3

 

[BigBeachBananas]

Hi All

Hoping someone can help me. I have two sheets, one with legacy contact email addresses and a new sheet where I want to enter new email addresses for contacts. On each sheet there is the option to add two emails, if a person has more than one email address. On sheet 2 where I will enter new contacts, I want a check column (f:f) to alert the user if the email address(es) entered on sheet 2 already appear on the legacy contacts on sheet 1. I therefore have 4 columns across two sheets to compare. So if the email address appears in either of the two columns in any order then it will return "YES" as per my example below. Can someone help me with a formula to perform this action? I'm assuming this would be some mixture of INDEX, MATCH, VLOOKUP? Thank you in advance.

Fruit Example.xlsm
	A	B	C	D	E	F	G
1	Sheet 1 - Legacy Emails			Sheet 2 - New Emails
2	Email 1	Email 2		Email 1	Email 2	Check Email
3	123@123.com	456@123.com		amy@company.com	amy2@company.com	NO
4	abc@abc.com	def@abc.com		def@abc.com		YES
5	test@test.com			456@123.com	123@123.com	YES
6	john@smith.co.uk	john2@smith.com		fred@flinstone.com		NO
7				test@test.com		YES
8				wilma@flinstone.com		NO
9				john2@smith.com		YES
10
11
Sheet3

Try this:
*Sheet 2 Cell F3:
=IF(COUNTIF(Sheet1!$A:$B,Sheet2!D3),"Yes","No") ----Check if Email 1 is in sheet 1
*Sheet 2 Cell G3:
=IF(COUNTIF(Sheet1!$A:$B,Sheet2!E3),"Yes","No") ----Check if Email 2 is in sheet 1
*Sheet 2 Cell H3:
=IF(OR(F3="Yes",G3="Yes"),"Yes","No") ----------------Check if either email 1 or email 2 was in sheet 1

 

[Tashat]

Try this:
*Sheet 2 Cell F3:
=IF(COUNTIF(Sheet1!$A:$B,Sheet2!D3),"Yes","No") ----Check if Email 1 is in sheet 1
*Sheet 2 Cell G3:
=IF(COUNTIF(Sheet1!$A:$B,Sheet2!E3),"Yes","No") ----Check if Email 2 is in sheet 1
*Sheet 2 Cell H3:
=IF(OR(F3="Yes",G3="Yes"),"Yes","No") ----------------Check if either email 1 or email 2 was in sheet 1

Hi BigBeachBananas - thank you so much. That's perfect!