Working out RAG status based on 3 columns

[theguyIsHere]

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 = 1, Amber = 2, Green = 3, Complete = 4 etc

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

Column A	Column B	Column C	Column D
Workstream	Sub-Workstream	RAG	Calculated RAG - Sub workstream
Workstream 1	Pilot	Green	Red
Workstream 1	Pilot	Red	Red
Workstream 1	Dev	Complete	Complete
Workstream 2	Test	Green	Amber
Workstream 2	Test	Amber	Amber
Workstream 2	Test	Green	Amber
Workstream 2	Req	Red	Red
Workstream 2	Dark	Green	Green
Workstream 2	Dark	Green	Green
Workstream 3	Yellow	Amber	Red
Workstream 3	Yellow	Red	Red
Workstream 3	Blue	Complete	Amber
Workstream 3	Blue	Amber	Amber
Workstream 3	Blue	Green	Amber

 

[theguyIsHere]

Thank you, that has fixed it

 

[DeficientOptimism]

Workstream	Sub-Workstream	RAG	Order	Calculated RAG	Grouped		Colour	Value
Workstream 1	Pilot	Green	3​	Red	Workstream 1 Pilot Red		Red	1​
Workstream 1	Pilot	Red	1​	Red	Workstream 1 Pilot Red		Amber	2​
Workstream 1	Dev	Complete	4​	Complete	Workstream 1 Dev Complete		Green	3​
Workstream 2	Test	Green	3​	Amber	Workstream 2 Test Amber		Complete	4​
Workstream 2	Test	Amber	2​	Amber	Workstream 2 Test Amber
Workstream 2	Test	Green	3​	Amber	Workstream 2 Test Amber
Workstream 2	Req	Red	1​	Red	Workstream 2 Req Red
Workstream 2	Dark	Green	3​	Green	Workstream 2 Dark Green
Workstream 2	Dark	Green	3​	Green	Workstream 2 Dark Green
Workstream 3	Yellow	Amber	2​	Red	Workstream 3 Yellow Red
Workstream 3	Yellow	Red	1​	Red	Workstream 3 Yellow Red
Workstream 3	Blue	Complete	4​	Amber	Workstream 3 Blue Amber
Workstream 3	Blue	Amber	2​	Amber	Workstream 3 Blue Amber
Workstream 3	Blue	Green	3​	Amber	Workstream 3 Blue Amber

In my version I think you may need Office 365 - and also the helper grid for the colors and order values.


Assume start column is "Workstream" which is column A & "Value" is column I is the last column for reference.

D2 =XLOOKUP(C2, H:H, I:I)
E2 = XLOOKUP(MINIFS($D$2:$D$15,$B$2:$B$15,B2),I:I,H:H)
F2 = A2&" "&B2&" "&E2

Copy down the above to the end of your data sets. You can rearrange F2 as you see fit to assign description.

 

[theguyIsHere]

A layer of complexity, if you had a 3rd column which had to be used to calculate the RAG status. Can that be added to the formula?

Here is my formula which I cannot get to work : -s

=IF(D2="Red","Red",VLOOKUP(MIN($A$2:$A$18=A2,IF($B$2:$B$18=B2,$E$2:$E$18,IF($C$2:$C$18=B2,$E$2:$E$18))),{1,"Red";2,"Amber";3,"Green"},2,FALSE))

 

[DeficientOptimism]

Workstream	Sub-Workstream	RAG	Order	Calculated RAG	Grouped		Colour	Value
Workstream 1	Pilot	Green	3​	Red	Workstream 1 Pilot Red		Red	1​
Workstream 1	Pilot	Red	1​	Red	Workstream 1 Pilot Red		Amber	2​
Workstream 1	Dev	Complete	4​	Complete	Workstream 1 Dev Complete		Green	3​
Workstream 2	Test	Green	3​	Amber	Workstream 2 Test Amber		Complete	4​
Workstream 2	Test	Amber	2​	Amber	Workstream 2 Test Amber
Workstream 2	Test	Green	3​	Amber	Workstream 2 Test Amber
Workstream 2	Req	Red	1​	Red	Workstream 2 Req Red
Workstream 2	Dark	Green	3​	Green	Workstream 2 Dark Green
Workstream 2	Dark	Green	3​	Green	Workstream 2 Dark Green
Workstream 3	Yellow	Amber	2​	Red	Workstream 3 Yellow Red
Workstream 3	Yellow	Red	1​	Red	Workstream 3 Yellow Red
Workstream 3	Blue	Complete	4​	Amber	Workstream 3 Blue Amber
Workstream 3	Blue	Amber	2​	Amber	Workstream 3 Blue Amber
Workstream 3	Blue	Green	3​	Amber	Workstream 3 Blue Amber

In my version I think you may need Office 365 - and also the helper grid for the colors and order values.


Assume start column is "Workstream" which is column A & "Value" is column I is the last column for reference.

D2 =XLOOKUP(C2, H:H, I:I)
E2 = XLOOKUP(MINIFS($D$2:$D$15,$B$2:$B$15,B2),I:I,H:H)
F2 = A2&" "&B2&" "&E2

Copy down the above to the end of your data sets. You can rearrange F2 as you see fit to assign description.

Since I can see you're on Excel 2019:

The below should work:

D2 = INDEX(I:I, MATCH(C2, H:H, 0))
E2 = INDEX(H:H, MATCH((MINIFS($D$2:$D$15,$B$2:$B$15,B2)), I:I, 0))
F2 = A2&" "&B2&" "&E2

 

[theguyIsHere]

Adding more workstreams to the formula above but it does not work. Why is this not working, pls?

=IF(E2="Red","Red",VLOOKUP(MIN($A$2:$A$18=A2,IF($B$2:$B$18=B2,IF($C$2:$C$18=C2,$F$2:$F$18))),{1,"Red";2,"Amber";3,"Green"},2,FALSE))

 

[Fluff]

noted. and thank you

Maybe you missed this part of the message

If you have posted the question at more places, please provide links to those as well.

In future please supply links to all sites where you have posted the question.

also posted here Working out RAG status based on 3 columns