Search for Identifier Words and Return a Specific Value

Hi everyone. I would really appreciate assistance.

Please see attached.

Ordered sheet
- Ordered Item column are items that were ordered. The first word is usually the manufacturer followed by the product name, but not always the case, and in some cases the words are joined together.
- Item Name column should show the item's name (what we name it in our system). This information should be pulled from the Item Names sheet.

Item Names sheet
- this shows all the Item Names, as well as the manufacturer, and word identifiers (the Id columns). Each Id is a word that identifies the product.

I was hoping for some formula or macro that does this:

-------------------------

- Search the first ordered item (Ordered, A2) for the manufacturer (Item Names, B2).

- If the manufacturer is not found, then move on to the manufacturer in the next row. If found, then search for Id1 (Item Names, C2).

- If Id1 is found, then return the appropriate item name (to cell B2 in Ordered). If not found, search for Id2. If found, return the appropriate name. If not found then search for Id3 .... and vice versa until Id9. If still not found, then move on to the next row, searching first the Manufacturer then the Ids, returning the appropriate item name.

-------------------------

Some of the ordered items may not even have spaces that separate words (like cell A8 and cell A9). So the formula/macro should hopefully still be able to still search for the Id despite that. The result is in the Result sheet.

Would really appreciate help on this one. It is driving me mental.