Compare lists when one column contains multiple entries

[Swilliams]

Hello,

I have several spreadsheets that I am needing to compare email addresses
from a column to a list of emails opened, simple enough, however the list i'm
comparing to contains many cells with multiple email addresses. Is there any
way to have excel look at each entry in a cell to compare with or do I have
to list each address in a different cell. Example:

A B
email1 email2 email3 Email5
email4 email5 Email6
email6 Email2

I need to have a formula that would compare column B to Column A and tell me
when it's found a matching email.

Is this possible?

Thanks.

 

[Dave Peterson]

I'm not sure what order you're comparing, but if you wanted to see if an email
address in B1 is in A1, you could use:

=countif(a1,"*"&b1&"*")>0

If you see True, then B1 is contained in A1. False means that it's not.

if you wanted to see if the email address in B1 was anywhere in column A:

=countif(a:a,"*"&b1&"*")>0

=====
You may have trouble if you have email addresses like:

(e-mail address removed) (in B1)
.... (e-mail address removed) ... (somewhere in column A)

But the formula could be modified for that, too.

 

[T. Valko]

Maybe this:

=IF(SUMPRODUCT(--(ISNUMBER(SEARCH(B2,A$2:A$4)))),"match","no match")

Copied down as needed.

 

[Swilliams]

Looks like this does what I needed Dave, thank you!