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

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

Identify if multiple values exist in one cell

djclements
Bronze Contributor
Apr 06, 2024
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
- Fcin144
  Copper Contributor
  Apr 06, 2024
  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.
  - djclements
    Bronze Contributor
    Apr 06, 2024
    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).
Lorenzo
Silver Contributor
Apr 06, 2024
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" )
- Fcin144
  Copper Contributor
  Apr 06, 2024
  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.
  - Lorenzo
    Silver Contributor
    Apr 06, 2024
    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?
V-GEe7
Brass Contributor
Apr 06, 2024
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.
Book1 (2).xlsx10 KB
- Fcin144
  Copper Contributor
  Apr 07, 2024
  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.
  - Lorenzo
    Silver Contributor
    Apr 07, 2024
    Fcin144
    
    in B2:
    =IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(A2, {"@bnymellon.com","@dreyfus.com"},,,1)), 0) ), "Yes", "No")
SophieT
Copper Contributor
Apr 07, 2024
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")
- Fcin144
  Copper Contributor
  Apr 07, 2024
  For Column A, it actual data, it is a value. It is not a formula.

Forum Discussion

Identify if multiple values exist in one cell

Share

Resources