categorize a list of text values using multiple criteria lists

[tarvajeh]

Search column A for any keywords in Columns C to F and if the string value in column A contains any of the keywords in any of the criteria lists, assign the header (Cat1/Cat2/Cat3/Cat4) to the corresponding cell in Column B

 

[tarvajeh]

List​ Category​ Cat1​ Cat2​ Cat3​ Cat4​ A valve is here​ Cat1​ Valve​ gold​ apple​ nothing​ Nothing here​ Cat4​ Pipe​ metal​ orange​ life​ Pipe is in here​ Cat1​ Ring​ silver​ berry​ Tom is here​ Root​ melon​ There is a ring here​ Cat1​ Epic​ life is short​ Cat1​ Short​ time is gold​ Cat2​ silver is cheaper​ Cat2​ metal is tough​ Cat2​ nothing more​ Cat4​ eat an apple​ Cat3​ berry is sour​ Cat3​ melon is big​ Cat3​ root is epic​ Cat1​

​

 

[tarvajeh]

If two keywords is found in any cell in column A, (eg. "Life is short" which can be assignd to Cat1 & Cat4), assign the first column's header (Cat1)

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)