vlookup partial string of cell to values

I'm sure your real life scenario will be more complex than this but you could use something like the below in Sheet1!B1 -- entered as an array using CTRL + SHIFT + ENTER:

Please Login or Register  to view this content.

Then copy down.

As mentioned pending the complexities of your real scenario this is likely to require alteration... as is it will return the associated value in Col B on Sheet2 for the FIRST match of Col A values on Sheet2 it finds in the text string in Ax on Sheet1 (where x = row number)... so a string containing BOTH Kettle & Iron would return 1600789 regardless of which "item" was listed first in the actual text string (eg "Kettle Iron" or "Iron Kettle") ... if Kettle was listed before Iron on Sheet2 it would return 2861863. I hope that makes sense.