Forum Discussion

meldons06's avatar
meldons06
Copper Contributor
Jul 06, 2020

How to know if a cell contains a value from the given list

Hello,   I would like to get a true or false value on column B if column C contains any value on the list of column E.      Can someone help me with the formula. This has been my frustrati...
excel
Formulas and Functions
TheAntony's avatar
TheAntony
Iron Contributor
Jul 06, 2020

meldons06 , in cell B2 try this formula:

=ISNUMBER(SEARCH(E2,C2))

 

meldons06's avatar
meldons06
Copper Contributor
Jul 06, 2020
Hi, i tried but did not get what I needed. but i appreciate the help, thank you.
  • TheAntony's avatar
    TheAntony
    Iron Contributor
    Jul 06, 2020

    meldons06 , can you upload a sample file with the data without any confidential info?

    • meldons06's avatar
      meldons06
      Copper Contributor
      Jul 06, 2020

      Hi TheAntony
      Here's a sample excel file. Columns C and D are the sample data while Columns F-G and I-K are the outcomes i would like to have.

       

      On columns F-G, cells on column F will highlight if it has a value from the list on column G

      While on outcome 2, Column I will get "TRUE" if column J has a value from the list on column K

       

      Thanks for helping! 

      • TheAntony's avatar
        TheAntony
        Iron Contributor
        Jul 06, 2020

        meldons06 , For Outcome1, I used conditional formatting to get the green highlights using this formula:

        =SUM(ISNUMBER(SEARCH(MID(F4,SEARCH("-",F4)+1,SEARCH("-",F4,SEARCH("-",F4)+1)-SEARCH("-",F4)-1),$G$4:$G$34))*1)=1

         

        For Outcome 2, it's the same formula but with referencing J and K instead of F and G:

        =SUM(ISNUMBER(SEARCH(MID(J4,SEARCH("-",J4)+1,SEARCH("-",J4,SEARCH("-",J4)+1)-SEARCH("-",J4)-1),$K$4:$K$34))*1)=1

         

        Let me know if this works.

Resources