Jul 05 2020 08:26 PM
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 frustration since yesterday and i wanna try my luck here. Hope somebody could help. Thanks in advance.
Jul 05 2020 08:37 PM
Jul 05 2020 09:04 PM
Jul 05 2020 09:07 PM
@meldons06 , can you upload a sample file with the data without any confidential info?
Jul 05 2020 09:36 PM
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!
Jul 05 2020 09:57 PM
@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.
Jul 05 2020 10:20 PM
Jul 05 2020 10:46 PM
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!
Jul 06 2020 03:40 AM
Jul 06 2020 10:48 PM
Much appreciated, Thank you for all the help guys. @TheAntony @Sergei Baklan
Jul 07 2020 02:07 AM
@meldons06 , you are welcome