Working out RAG status based on 3 columns

[IamThatGuy]

Trying to summarise the RAG status in one field but unsure how I do that



Column D needs to make sure it groups Column A and then column B and then assign the lowest RAG from column C

For example, For Workstream 1, 'Pilot' appears twice in column B with the lowest RAG status of Red. So Column D automatically needs to assign Red to Workstream 1, pilot sub workstream.

If it is easier then RAG status could be assigned a number too. For example: Red becomes 1, Amber = 2, Green = 3, Complete = 4 etc

Table with dummy data below. Grateful for any assistance on this, please.