I am trying to compare text in 2 columns and see if there is a match

[srienstra]

I need a formula to compare b2 (part number) against data within a column found on another sheet which is a description containing that part number from B2. The description may contain the part number at the beginning, middle or end of the description. Once there is a match, I need to take the part number from the 2nd sheet that tied in to the description and add that to my original sheet. What I am comparing is data from two part companies. 1 company uses an original part number from the manufacturer and the second company may have that original number in their description.sample sheet 1.xlsxsample sheet 2.xlsx

So in sheet 1 I would like to use column c for result. I need to look at contents in column B on sheet 1 and compare it to the contents of column B on sheet 2. There are 2 matches sheet1 b7 = sheet2 b21 as well as sheet1 b13 = sheet2 B20. I then required the corresponding part# in column A to be inserted in sheet1 column c. So since B13 is a match to B20 I need part #19(a20) inserted in to C13

thank-you and sorry for the comfusion

[Pete_UK]

Attach a sample workbook, as that might be clearer to understand.

Pete