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

Occasional Contributor



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 frustration since yesterday and i wanna try my luck here. Hope somebody could help. Thanks in advance. 

10 Replies

@meldons06 , in cell B2 try this formula:



Hi, i tried but did not get what I needed. but i appreciate the help, thank you.

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

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! 

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



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



Let me know if this works.

Correct the highlighted errors and try again. There was an error while attempting to post your message. Try again in a few minutes.

Hi @TheAntony I truly appreciate your help. However, the formula you gave only applies to the sample file I did but not to the file i'm working on (after adjusting formula cells accordingly)


Here's a replica of what i am working on. The values on data 2 may be found anywhere in the text on column B (may be on the first, mid, or last)



Sorry if it's too complicated, thanks in advance!



As variant


that could be


Much appreciated, Thank you for all the help guys. @TheAntony @Sergei Baklan