Vlookup of string within a longer string

Hi,

Here is the format of the Data i have in a excel workbook:

Sheet1 has 1 Column with data in this format
Column1 Column2 (my desired output based on value in Sheet2)
FTS-123-1 P1
FTS-345-2 P3
.
.

Sheet2 has two columns. Each cell of column1 here can have multiple values of the kind in sheet1:column1 space separated (FTS-123-1 FTS-6789-4).
Column1 Column2
FTS-123-1 FTS-6789-4 P1
FTS-256-8 P2
FTS-345-2 FTS-789-6 P3


what i need to do here is: in sheet1:column2 i want to show the value from sheet2:column2 IF if a corresponding value in sheet2:column1 matches with the sheet1:column1 value.

would appreciate any help on this. i hope my question is clear ...

thanks,

Thanks much. That works. I need a modfication to this as well.

If the two sheets are in separate workbooks how can I accomplish the same task?

thanks,

Thanks Again. That worked.