Proceeding sibling

Hi there   I'm looking for some help with an excel formula. Have tried myself but is beyond my capabilities.   In the table below, I need a formula for Column D. If I were to characterize the rul...