# Formulae Question

#### lhilton

##### New Member
I have a similar question to one I posted yesterday.

I have 3 projects that I am currently managing a, b and c. The projects have 3, 4 and 2 initiatives running within them respectively and each of the initiatives has a RAG status (red, amber or green). The project code (a, b or c) is given in cells A1:A9 and is not neccesarily sorted. The corresponding initiative number is listed in cells B1:B9. Cells C1:C9 contain the corresponding RAG status for each initiative. Cells E1:E3 list the projects a, b and c. In cells F1:F3 I am trying to return a rag status for each of the projects based on the following logic:

If any of the initiatives within the project has a 'red' status, that project should aslo have a 'red' status. If none of the initiatives has a 'red' status, but at least one 'amber' status, that project should have an 'amber' status. If all of the initiatives within a project have a 'green' status, that project should also have a 'green' status.

### Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

#### lhilton

##### New Member
Here's the simplified example:

Project Initiative RAG
a 1 Green
c 2 Red
a 3 Green
b 4 Green
c 5 Green
b 6 Green
b 7 Green
a 8 Amber
b 9 Green

#### fairwinds

##### MrExcel MVP
Hi,

All RAG must be filled in.
Book1
ABCDEF
1ProjInitiativeRAG
2aa1RaR
3aa2AbA
4aa3GcG
5bb1G
6bb2G
7bb3A
8cc1G
9cc2G
10cc3G
Sheet1

#### SIXTH SENSE

##### Well-known Member
hi!
I dont undertand you clearly! but check this out!
Book1
ABCDEFGHI
1projectinitiativesstatusprojectstatusredgreenamber
2a3amberared201
3a4redbamber021
4b2greencgreen020
5b3green
6c3green
7c4green
8a2red
9b4amber
Sheet6

##### MrExcel MVP
lhilton said:
Here's the simplified example:

Project Initiative RAG
a 1 Green
c 2 Red
a 3 Green
b 4 Green
c 5 Green
b 6 Green
b 7 Green
a 8 Amber
b 9 Green

Although not fully tested...
Book2
ABCDEF
1ProjectInitiativeRAGaAmber
2a1GreenbGreen
3c2RedcRed
4a3Green
5b4Green
6c5Green
7b6Green
8b7Green
9a8Amber
10b9Green
Sheet1

The formula in F1 is:

=INDEX({"Red","Amber","Green"},MATCH(TRUE,ISNUMBER(MATCH({"Red","Amber","Green"},IF(\$A\$2:\$A\$10=E1,\$C\$2:\$C\$10,""),0)),0))

which must be confirmed with control+shift+enter instead of just with enter.

Replies
2
Views
134
Replies
3
Views
216
Replies
15
Views
555
Replies
1
Views
38
Replies
0
Views
90

1,172,161
Messages
5,879,373
Members
433,423
Latest member
debbers

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back