Compare 2 columns and return the best match

Belated Merry Christmas everyone!

I've been racking my brains about this problem but I can't find a solution, and I am freaking out a bit.

Please refer to the attached file.

On the problem tab, there are 3 columns: Order List, Item per Item List , and Item List .

Basically, I am hoping for this to happen ... for each cell on the Order List column, refer to the Item List column and choose the best match and put it into the Item per Item List column. The desired result is in the Result tab.

The problem is that the Order List column has all this unnecessary stuff that that makes it hard to match. If I do it manually, I can determine "Okay, there's a "Blackberry" and a "9900" in there so it should be a Blackberry 9900 per the Item List." But I'm having a really hard time translating that into a formula or vba code.

Anyways, I would really appreciate some assistance on this. Thank you.