Forum Discussion

Fcin144's avatar
Fcin144
Copper Contributor
Apr 06, 2024

Identify if multiple values exist in one cell

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 ...
Excel
  • Lorenzo's avatar
    Lorenzo
    Apr 07, 2024

    Fcin144 

     

    in B2:

    =IF( SUM( IFERROR( SEARCH("@", TEXTSPLIT(A2, {"@bnymellon.com","@dreyfus.com"},,,1)), 0) ), "Yes", "No")
djclements's avatar
djclements
Bronze Contributor

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's avatar
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's avatar
    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).

Resources