Searching for a string in an array of strings

Hi,

I'm having a little trouble with this one. What I'm trying to do is to use Sheet1 as a reference sheet, then Sheet2 as an order sheet.

Sheet2 has all the orders, but they are all strings, with the item code in them. What I want to do is extract the code numbers (potentially hundreds of numbers), then compare them against the reference sheet (Sheet1), again potentially hundreds of numbers. The item code may be anywhere in the string.

What I want returned is the Item name based on the reference sheet.

So basically:
1. Get item code from Sheet2
2. Compare item code in Sheet1
3. Return item name from Sheet1. Place it in Sheet2

Is there any way to do this as a formula in a cell?