I need help wrinting a formula for the below.

Sheet 1
A1=Style Number
B1=Size (text value)
C1=part number (formula result)

Sheet2 contains the data.
There are multiple entries of the style number (A1) on sheet 2. The size is
actually part of the TEXT description of that style. I need the formula to
give me the part number of that Size in that Style number.

Is there a way to write the formula to look for the Style number, then look
in that description for the text value and when they match, give me the part
number 1 column over?