[SOLVED] Searching A Cell for multiple text strings (a whole list) with one formula?

Hello, I am new to the forum and I've searched around, but I didn't see a solution to my problem that specifically works in the way I am looking for it to work, so I figured I'd ask here and see if there is a solution. I've been wrecking my brain for the best solution for this. Here Goes:

I have one sheet (below) where I enter Branded Terms (think Apple, Ipod, iTunes etc....)

Branded Terms cells.jpg


Then I have another sheet (below) where I import a list of SEARCH TERMS, you'll see those in the leftmost column. These can number into the thousands of search terms.

Search Terms cells sheet.jpg

I am trying to find the Best Solution to Search each cell of SEARCH TERMS for the Branded Terms I entered in the list on the other sheet. Once the formula identifies that none of the Branded terms are in the cell then it should move the cell contents into the gray area next to it. Obviously I know how to use the Search and Find functions, but I haven't found a way to used them for mulitple words in the list i.e. ( search(OR x,y,z,m,n,o,p)) A Search OR doesn't seem to work for an entire list. As you can see the list of terms that I enter in the first sheed MAY be 5 terms OR 100 terms and I need that formula to be dynamic in that it searches the cell for ALL of the terms in the list (that's why I made it into a table, but maybe this isn't the way to go?) I know I can string together a long If formula with Static Search values but that wouldn't work at all. I know I've tried a few things like Sumproduct array formulas but there seems to be a rub there too. It only returns an error if ALL of the words aren't/are found. I need the formula to search for ALL of the terms in the initial list and if even ONE of them are found for it to return an error.

Please let me know if I'm not being clear.

Thanks for any help you can provide, I've been working on this for weeks and I know how to do ham-handed workthroughs, but they still take all day and are only reliable as your eyes. I'm trying to write this formula so that I can strip out ALL NON-Branded search terms into one list.

**One other thing! Of course the initial list is text that I enter and will change whenever I have a new Brand I am trying to scrape search terms for, so I can't do static values in the formula i.e. (SumProduct(List,"apple","ipod","ipad"). The formula is going to have to reference cells or Tables etc so that the contents of those things can be changed at will.


Thanks,

IvanW