Automatically find text that matches between sheets

[Rhi]

Hi!

I'm pretty good with Excel but struggling with this one.

I have an Excel sheet that contains a list of ingredients that can go into products. I then receive excel files from customers that state the ingredients they are using for their specific product in. At the moment I do a manual check of each of their ingredients using ctrl+f to see if it appears on my master list. This works perfectly but is user intensive. I'm thinking there could be a way to get Excel to do this for me.

I know about conditional formatting options but think that will only work for exact cell matches. That is no good because the applicant may list multiple ingredients in one cell on their sheet.

So I basically need Excel to search all text in the client's sheet like a ctrl+f and higlight where text appears in my master sheet.

Is this impossible because it reduces the text to individual letters?

I know I could do text to columns first to separate out each ingredient but that is a manual step again.

The alternative is to require clients to present their data in a more user-friendly way for me but that would cause them more difficulty and there will always be one that doesn't do it how requested.

If anyone has any ideas your expertise would be much appreciated :-)

Rhi

[ChemistB]

Hard to say without an example of a few possible searches and what your master sheet looks like.
Some possible conditional formatting formulas

If the request is a substring of what’s in your mastersheet
Putting the request in A1 of sheet1
Conditional formatting in mastersheet range selected beginning in A1

Please Login or Register  to view this content.

format as desired.

If the mastersheet is a substring of the request

Please Login or Register  to view this content.

format as desired