How to search sub-strings in a lookup table

Hello everyone,

I would like to ask for help from the Excel experts out there. I would like to find a way to lookup a sub-string in a lookup table and pick the highest score from this table if two sub-strings in a row are found in the table.

For example, let imagine we have Sheet1 with a set of substrings separted by a space in Column A as depicted below:

Sheet1
Col A
Str1 Str2 Str3
Str4 Str5
Str6 Str7 Str8

I would like to enter your formula in Column B of Sheet1 so it would display the correct score. Please note that the number of sub-strings in each row of Column A may vary.

Now, let's suppose Sheet2 has the following values:
Sheet2
Col A ColB
Str2 10
Str7 10
Str8 20

Therefore, we would expect the following answers:

Sheet1
Col A
Str1 Str2 Str3 10
Str4 Str5
Str6 Str7 Str8 20

Any help on this will be much appreciated.

Apologies. The scores 10 and 20 would be displayed under Column B in the expected answers section above. Therefore, there is not need to join the scores as text to the sub-strings in Column A.

Thank you Special-K for looking into this. Much appreciated.

Thanks a million KOKOSEK. This is pretty close to what I am looking for but unfortunately the formula does not seem to take the highest score when two sub-strings are found in the lookup table. Looking at the file you uploaded, the formula in the last row ideally should give 20, not 10. I realize this might be difficult to achieve but if you have any ideas, it would be much appreciated.

Thank you Bo_Ry for providing the alternative approach.

Also thanks to everyone for the efforts. Much appreciated.