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.

Attached Files

RAG Test.xlsx (9.4 KB, 7 views)