yogi_Conditionally Format 'Ingredients To Avoid' That Are In 'Ingredients List'

Google Spreadsheet   Post  #2230

Yogi Anand, D.Eng, P.E.      ANAND Enterprises LLC -- Rochester Hills MI     Aug-26-2017
question by: jorasdou

https://productforums.google.com/forum/?utm_medium=email&utm_source=best_answer_notification_button#!topic/docs/xUn8Kb5GZvs;context-place=forum/docs

Possible to enter a list of items in one cell and have matches in other cells highlight?

 

Apologies in advance, because I'm not tech- or formula-savvy. I have a skin condition that requires me to be avoid certain ingredients; I can usually remember most of them, but sometimes it's hard to remember all of them. While I'm shopping for products online (because I'm still trying to find things that help), I'm having to read through ingredient lists to look for problematic items, usually multiple times in order to check for each thing.

I'm wondering if there's a way to set up a Sheet and formula for this:

-Ingredients To Avoid are already listed in individual cells

-I copy/paste a product's full ingredients list into a specific cell (a large merged cell)

-Any word matches between the two lists would result in that ingredient's cell being highlighted on the Ingredients To Avoid list

I set this up for example -- the full list of ingredients has Alcohol Denat, Castor Oil, and Fragrance, so three cells are colored in:

The Ingredients To Avoid list isn't complete, so I'd need to know how to change the formula to incorporate the additional items. But is this even possible? Or will it not work due to the product's full list of ingredients not being listed in individual cells?