Simplification of text strings using common terms

I've got a very large data set where some of the columns contain sets of phrases or sentences such as "Peripheral-membrane protein. Single-pass membrane protein." What I am trying to do, and failing quite spectacularly, is simplify the data in each cell of the column by comparing the text in the text-heavy cells on the main worksheet with a simple list of keywords on another worksheet. More specifically, the keyword worksheet has two columns, a list of "look up" words (i.e. peripheral-membrane) and a list of "output" words (i.e. membrane protein). My hope is to create a formula that can compare the "look up" words to my text strings in the main data set and return the "output" word in to the adjacent cell. The bottom line is that I need to try to consolidate all of the phrases in the text-heavy cells to be able to better analyze the information contained within. I hope I've described this clearly.

Any help will be greatly appreciated - I've been learning so much about Excel from all these forums and decided this was the place to come and ask this question. Thanks in advance.

Cheers,

WC

put this in D2 of Main Worksheet and drag-fill down:

Please Login or Register  to view this content.

UPDATE:

2^20 is just some LARGE number; SEARCH attempts to find a string in another string and returns the corresponding location_number (in the above construct, it returns an array of location_numbers). 2^20 should be larger than that location_number, typically people use 99^99 or 1E307 (which is 10 to the power of 307).