Match value to parent ABOVE containing specific text

I would like to perform a check to see where a child value sits within a parent hierarchy from a database download. The child value always starts with “C” the parent with an “S”. All the data is contained within the same column.

I am looking for a formula rather than macro to locate the child value - and then look UPWARDS above it, to find the next instance of a value starting with ‘S’ (its parent).

I can find the initial ‘C’ value using an index match, but cannot work out how to then locate the next instance of an ‘S’ value above it.

Any help appreciated.

Example sheet attached: Column B contains raw data, E contains values I wish to lookup, F contains the correct returned parent I wish to corroborate but don’t know how to.

These both look like excellent solutions, I will give them some time later after meetings.

Brilliant! 3 interesting, viable ways to tackle this. Im new to using excel for logic formulae, but this will make some big time gains. NFL - thank you, did not know a lookup could work like that, Richard that is an elegant solution, Special K - impressive, this is testing my understanding to the limit!

Really appeciate your time and help!