if one of many different text strings is found in a cell apply a corresponding text string

I have two excel sheets,

the first one is a parts list with two columns: column A equipment description and column B subcategory

the second sheet has two columns: column A keywords and column B subcategory

I want a function that will apply a subcategory to sheet 1 column B if the keywords in sheet 2 column A are found in Sheet 1 column A BUT the key words are mixed in with other words.

examples of the part descriptions are as follows:
wicket gate bolt
screw for wicket gate
8" wicket gate pipe

my keyword in this case would be "wicket gate"

I've tried using INDEX, LOOKUP, VLOOKUP but I've had no luck because the keywords are seldom by themselves.

I don't understand exactly what you are doing but you should probably be using wildcard(s) for your search value.

Like: =VLOOKUP("*wicket gate*",range,2,0)

which would find the first value in the searched range that contains wicket gate.

Yeah I tried that but I don't think you can apply a wildcard to a search range. The way my problem is set up you would need to use
=VLOOKUP("wicket gate","*"&range&"*",2,0) which doesn't work.

This should explain my problem better; I want to fill the blank column on this sheet:
parts list.PNG

And this is the information that I want to use to do it:
subcategories.PNG

Nevermind I figured it out. I wrote a function in the VBA editor thingy.

Please Login or Register  to view this content.

this does exactly what I wanted. I couldn't for the life of me find any built in functions that could do this.

Thanks for the help.