Identify if multiple values exist in one cell

[Fcin144]

I am trying to determine if an email address is for a client, or it is not.

So, for example our company email addresses are @nbc.com or @abc.com

I would like to know if any incoming email are other than the above (@nbc.com or @abc.com) in cell A2 and A3

I tried this formula =IF(OR(COUNTIF(A3,{"*nbc*","*abc*"})),"No","Yes") 

but it returned "No". The cell has @abc.com listed in the cell and  I would like it to return "Yes".

Thank you

[#2]

@Fcin144 What version of Excel are you using? With Excel for MS365 or Excel for the Web you could try the following:

=IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(A2, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")

You could also use the MAP function to "spill" the results for the entire list of email addresses. For example:

=MAP(A2:A10, LAMBDA(v, IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(v, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")))

Sample Results

[#3]

@Fcin144 

With older version of Excel (but on Mac)

=IF(
  SUM( IFERROR( SEARCH({"@abc.com","@nbc.com"}, B3)^0, 0) )
  <> COUNTA( FILTERXML("<t><w>" & SUBSTITUTE(B3, ",", "</w><w>") & "</w></t>", "//w") ),
  "Yes", "No"
)

[#4]

@djclements 

Thank you for the reply. I am using Web Version 365.

I tried your recommended statement however it returned No when either abc.com or nbc.com was found. In addition, when any other email address was found it returned Yes. 

=IF(OR(ISNA(XMATCH(TEXTAFTER(TEXTSPLIT(A2, ","), "@"), {"abc.com","nbc.com"}))), "Yes", "No")

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

Thanks again in advance.

[#5]

Thank you for the reply. I am using Web Version 365.

I tried your recommended statement however it didn't work.

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

Just an fyi, the field "B3" in your example is part of a table. I am not sure if that changes anything.

Thanks again in advance.

[#6]

@Fcin144 

I would like ...to return "Yes" when any other email address besides abc.com or nbc.com is found:

in C3:

=IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(B3, {"@abc.com","@nbc.com"})), 0) ), "Yes", "No")

Works better/does it?

[#7]

@Fcin144 , Try using the below formula to actually give you a number showing how many client emails are there.

=(LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@abc.","."),"@nbc.",".")))/4

However if you want only yes or no then use the below.

=IF(((LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@abc.","."),"@nbc.",".")))/4)>0,"Yes","No")

PFA the file for your reference. 

[#8]

@Fcin144 

---

@Fcin144 wrote:

I tried your recommended statement however it returned No when either abc.com or nbc.com was found.

---

The formula only returns "No" when ALL email addresses in the cell are company accounts (ALL domain names are either "abc.com" or "nbc.com").

---

@Fcin144 wrote:

In addition, when any other email address was found it returned Yes.

...

I would like the Client Field in the spreadsheet that stores the cell and the statement to return "Yes" when any other email address besides abc.com or nbc.com is found using the statement.

---

That's exactly what the formula does... when any non-company email address is found, it returns "Yes".

I'm afraid your question is unclear. What you want appears to conflict with what you don't want. It would help if you could provide a larger sample set that accurately and completely reflects your actual data, showing what the formula is currently returning for each scenario, plus a third column showing the expected results (manually type what the result should be and why).

[#9]

@V-GEe7   @Lorenzo @djclements 

Thanks for your reply. It is working however when I convert the @nbc.com and @abc.com email address conversion to my test case it's not working. I have supplied the actual test case in the screenshot below. I really appreciate everyone's help. 

[#10 · Accepted solution]

@Fcin144 

in B2:

=IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(A2, {"@bnymellon.com","@dreyfus.com"},,,1)), 0) ), "Yes", "No")

[#11]

Is the data in column A actual data? as in a value or is it a formula to populate the cell? if its not a value you will find it hard to get a formula to recognize the data - unless you put value at the front. eg. if(value(or(countif(a3,{"*nbc*","*abc*"})),"No","Yes")

[#12]

For Column A, it actual data, it is a value. It is not a formula.

[#13]

Thank you soooooo much for your patience and expertise, it is truly appreciated. It worked !!!!

[#14]

@Fcin144 ,

As the number of characters change you may also need to change the number in the formula, try the below, for the number of client emails

=(LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@dreyfus",""),"@bnymell","")))/8

  and the following formula for yes/no

=IF(((LEN(A1)-LEN(SUBSTITUTE(A1,"@","")))-(LEN(A1)-LEN(SUBSTITUTE(SUBSTITUTE(A1,"@dreyfus",""),"@bnymell","")))/8)>0,"Yes","No")

[#15]

Thanks for providing feedback