Compare email addresses in one column to account name in another, then produce a result.

[bluegrassflash]

[FONT=Helvetica Neue, Helvetica, Arial, sans-serif]Hello All,

[/FONT]Really need some help!

[FONT=&quot]Assume Column A is an Account name and Column B is an email address. Basically, I want something that analyses the email address and compares it to the name of the account in Column A. If the email address contains any word from the Account name in Column A, or it's similar, then it needs to produce in Column C something that says, "Do not Contact."[/FONT]

[FONT=&quot]But if NOT, then Column C needs to say, "OK to Contact"[/FONT]

[FONT=&quot]Example:[/FONT]
[FONT=&quot]AOL, Inc. [/FONT]Maverick.Goose@acme.org[FONT=&quot] -- OK to Contact[/FONT]
[FONT=&quot]AOL, Inc. [/FONT]James.Smith@aol.com[FONT=&quot] -- Do not Contact (b/c "aol" is included in account name.)[/FONT]

[FONT=&quot]What I'm trying to do is this: I have accounts that have certain IT managers. If the IT managers have emails that are the same name as the account, it's likely they work for that same business. BUT, if the IT managers have different email addresses than the account, then those individuals must work OUTSIDE of the business. It's THOSE who are OUTSIDE of the business that I'm trying to narrow down.[/FONT]

[FONT=&quot]Thanks in advance![/FONT]

 

[jtakw]

Hi,

This is one way, but it may not be fool proof:

Excel 2010
	A	B	C	D
1	AOL, Inc.	Maverick.Goose@acme.org		OK to Contact
2	AOL, Inc.	James.Smith@aol.com		Do Not Contact
3	Red Cross	John_Doe@Red.net		Do Not Contact
Sheet6

Cell Formulas
Range	Formula
D1	=IF(ISNUMBER(SEARCH(LEFT(MID(B1,FIND("@",B1)+1,255),FIND(".",MID(B1,FIND("@",B1)+1,255))-1),A1)),"Do Not Contact","OK to Contact")

 

[bluegrassflash]

Thanks for the tip! On this I keep getting everything saying "OK to Contact" when I know that's not true. In my specific spreadsheet, A2 is the start of the account names... and F2 is the email address and H is the returned value. Here is what I did after modifying yours. =IF(ISNUMBER(SEARCH(LEFT(MID(A2,FIND("@",A2)+1,255),FIND(".",MID(A2,FIND("@",A2)+1,255))-1),G2)),"Do Not Contact","OK to Contact") Is this correct?

 

[jtakw]

Thanks for the tip! On this I keep getting everything saying "OK to Contact" when I know that's not true. In my specific spreadsheet, A2 is the start of the account names... and F2 is the email address and H is the returned value. Here is what I did after modifying yours. =IF(ISNUMBER(SEARCH(LEFT(MID(A2,FIND("@",A2)+1,255),FIND(".",MID(A2,FIND("@",A2)+1,255))-1),G2)),"Do Not Contact","OK to Contact") Is this correct?

Your modification of my formula do Not match your description here, you say the Email Address is in F2, what's G2 doing in the formula?
If your Account names starts on A2, and the Emails starts on F2, use this:

=IF(ISNUMBER(SEARCH(LEFT(MID(F2,FIND("@",F2)+1,255),FIND(".",MID(F2,FIND("@",F2)+1,255))-1),A2)),"Do Not Contact","OK to Contact")