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")
Lorenzo's avatar
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's avatar
    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's avatar
      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?

Resources