Returning most appropriate value from a list

[GW1994]

I am trying to populate columns C and D (main and secondary) by extracting the most appropriate value from its corresponding row in column B. Column B contains lots of values separated by commas. I am trying to see is it possible for example in C2 (sheet1) to look at the list located at sheet 2 column A and return the value "connector", in cell C2.
The issue here is that if we look at for example row 3, sheet 1. The value I need returned in C3 is "Hose Flexible Connector", but with my formula I can only manage to get it to return all values in this list which are Hose, Hose Flexible, Hose Flexible Connector.
Is it possible to just return the most appropriate match I.e. Hose Flexible Connector.

Have tried IF(OR(Count formulas using wildcards such as *
The closest I have gotten is by using the following formula:
=TEXTJOIN(", ", TRUE, IF(COUNTIF(B3, "*"&Sheet2!$A$2:$A$10&"*"), Sheet2!$A$2:$A$10, ""))


Any help would be much appreciated.

Thanks

[bebo021999]

Assuming:
- If in main list, return main, if not, return Secondary, if not, blank cell
- If duplicate, for instance:
Hose Flexible Connector
Hose Flexible
Hose
one with more length is choosen. (Hose Flexible Connector)

Try:

Please Login or Register  to view this content.

[RaulSerg]

Hmm, shouldn't the correct answer for 15 be Sprinkler Head instead of Sprinkler?

And for 10, how do you get for the correct answer to be Reel?

Have a look at the attached.

[GW1994]

Exactly what I was looking for, thank you very much.

[AliGW]

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED.

Also, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of all those who offered help.