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

%3CLINGO-SUB%20id%3D%22lingo-sub-1504928%22%20slang%3D%22en-US%22%3EHow%20to%20know%20if%20a%20cell%20contains%20a%20value%20from%20the%20given%20list%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1504928%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20like%20to%20get%20a%20true%20or%20false%20value%20on%20column%20B%20if%20column%20C%20contains%20any%20value%20on%20the%20list%20of%20column%20E.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22meldons06_1-1594005884202.png%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F203434i2D4E7ECD94869654%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20title%3D%22meldons06_1-1594005884202.png%22%20alt%3D%22meldons06_1-1594005884202.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20someone%20help%20me%20with%20the%20formula.%20This%20has%20been%20my%20frustration%20since%20yesterday%20and%20i%20wanna%20try%20my%20luck%20here.%20Hope%20somebody%20could%20help.%20Thanks%20in%20advance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1504928%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Occasional Contributor

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. 

 

meldons06_1-1594005884202.png

 

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:

=ISNUMBER(SEARCH(E2,C2))

 

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:

=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.

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)

meldons06_0-1594014357126.png

 

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

 

@meldons06 

As variant

image.png

that could be

=(SUM(COUNTIF(O4,"*"&$P$4:$P$12&"*"))-COUNTBLANK($P$5:$P$12))>0

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