Search for Keywords and Return the Appropriate Item

Hi everyone. I am having a really tough time figuring out whether this could be done or not, and I am just lost. Basically, we often get mobile phone orders in Excel format but the items listed are in a different format from the ones we have in our inventory system.

Please refer to the attachment.

In the Item List tab, you will find under the Item List column, a list of phones as they are named in our inventory system.

However, in the Order list tab, you will see a list of odered phones that are either named in a different format or are a different variant of the same phone on the Inventory Item list.

So what I did was create keywords that are associated with a specific phone. So for example, for HTC Touch Dual - P5310 (cell A7 in the Item List tab) , it's associated keywords are:

P5310 , Touch Dual , and Neon 400 . HTC is the Manufacturer.

Now in cell A4 of the Order List tab, it says HTC P5310 SIL . This should therefore be assigned HTC Touch Dual - P5310 as per the inventory item list.

So basically, I was hoping for some formula or macro that would look at the ordered item, then check if it contains the manufacturer name, then check if it contains any one of the keywords, then return the associated inventory item to the cell beside the order item.

So for HTC P5310 SIL in cell A4 in the Order List tab, it sees that it's manufacturer is HTC ... so now check if it contains any of the keywords. Since it contains P5310, then it has identified that it is an HTC Touch Dual - P5310 as per the inventory item list. And vice versa. The result should be something like in the Result tab.

I hope I'm not being confusing. Anyways, any assistance would be much appreciated. This is a huge problem for me and I'm getting really really desperate.