Working out RAG status based on 3 columns

[exceltripping]

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.

[AliGW]

Are you really still using Excel 2003???

There are instructions at the top of the page explaining how to attach your sample workbook.

A good sample workbook has just 10-20 rows of representative data that has been desensitised. It also has expected results mocked up, relevant cells highlighted and a few explanatory notes.

[exceltripping]

Thanks for the quick reply. I have now attached a sample excel with dummy data.

I am using Excel 2019 on a Mac

[AliGW]

Administrative Note:

Please update your forum profile with your Excel version.

We would very much like to help you with your query, however it has been brought to our attention that the same query has been posted on one or more other forums and you have not provided the required cross-post link(s) here.

Please see Forum Rule #3 about cross-posting and adjust accordingly. Read this to understand why we (and other sites like us) consider this to be important.

(Note: this requirement is not optional. No help to be offered until the link is provided.)

[exceltripping]

Thank you and I wasn't aware.

LINK to the other forum post: https://www.mrexcel.com/board/thread...lumns.1196203/

[AliGW]

Thank you.

[AliGW]

You still haven't updated your forum profile with the correct version of Excel - please do this NOW.

[exceltripping]

Solution here: https://www.mrexcel.com/board/thread...lumns.1196203/