I have a list of unique reference numbers in column A
In column B I have the refence numbers from column A jumbled up and the same number can appear multiple times or not at all.
In column C I mark if the reference number in column A is a Bronze or Silver Class

In column D I now need the formula to do the following calculation.
Check if the reference number in column A appears anywhere in column B, if it does check if the reference number which it appears next to is a Bronze or Silver Class. If it is Bronze add 10 if it is Silver add 20.

The refernce number in column A will only appear once in column A but multiple times (or not at all) in column B

Can anyone help with this formula