Project Status Dashboard - HELP!

[stanleytheyak]

I have a matrix of possible statuses based on three criteria. Budget, Schedule and Scope.

What I'd like to do is create a formula that takes the project's statuses in these three categories and return one of five overall statuses.

The matrix looks like this:
<table border="1" cellpadding="0" cellspacing="0"><tbody><tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td> </td> <td>B</td> <td>C</td> <td>D</td> <td>E</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Overall Status</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Budget</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Schedule</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</td> <td>Green</td> <td>Under Budget</td> <td>In Progress</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</td> <td>Green</td> <td>Under Budget</td> <td>Completed</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</td> <td>Green</td> <td>At Budget</td> <td>In Progress</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</td> <td>Green</td> <td>At Budget</td> <td>Completed</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</td> <td>Green/Yellow</td> <td>At Budget</td> <td>Delayed</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</td> <td>Green/Yellow</td> <td>Under Budget</td> <td>Delayed</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</td> <td>Green/Yellow</td> <td>Under Budget</td> <td>Not Started</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</td> <td>Green/Yellow</td> <td>At Budget</td> <td>Not Started</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</td> <td>Green/Yellow</td> <td>Over Budget</td> <td>In Progress</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</td> <td>Green/Yellow</td> <td>Over Budget</td> <td>In Progress</td> <td>In Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</td> <td>Yellow</td> <td>Under Budget</td> <td>In Progress</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</td> <td>Yellow</td> <td>At Budget</td> <td>In Progress</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</td> <td>Yellow</td> <td>Under Budget</td> <td>Completed</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</td> <td>Yellow</td> <td>At Budget</td> <td>Completed</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</td> <td>Yellow Red</td> <td>Under Budget</td> <td>Delayed</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">44</td> <td>Yellow Red</td> <td>At Budget</td> <td>Delayed</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">45</td> <td>Yellow Red</td> <td>Over Budget</td> <td>In Progress</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">46</td> <td>Yellow Red</td> <td>Over Budget</td> <td>Completed</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">47</td> <td>Yellow Red</td> <td>At Budget</td> <td>Not Started</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">48</td> <td>Yellow Red</td> <td>Under Budget</td> <td>Not Started</td> <td>Out of Scope</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">49</td> <td>Red</td> <td>Over Budget</td> <td>Delayed</td> <td>Out of Scope</td></tr></tbody></table>

The spreadsheet will look something like this:
<table border="1" cellpadding="0" cellspacing="0"><tbody> <tr style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"> <td>
</td> <td>G</td> <td>H</td> <td>I</td> <td>J</td> <td>K</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">28</td> <td> </td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Budget</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Schedule</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Scope</td> <td style="FONT-WEIGHT: bold; TEXT-DECORATION: underline">Status</td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">29</td> <td>Project 1</td> <td>Under Budget</td> <td>In Progress</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">30</td> <td>Project 2</td> <td>Under Budget</td> <td>Completed</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">31</td> <td>Project 3</td> <td>At Budget</td> <td>In Progress</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">32</td> <td>Project 4</td> <td>At Budget</td> <td>Completed</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">33</td> <td>Project 5</td> <td>At Budget</td> <td>Delayed</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">34</td> <td>Project 6</td> <td>Under Budget</td> <td>Delayed</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">35</td> <td>Project 7</td> <td>Under Budget</td> <td>Not Started</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">36</td> <td>Project 8</td> <td>At Budget</td> <td>Not Started</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">37</td> <td>Project 9</td> <td>Over Budget</td> <td>In Progress</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">38</td> <td>Project 10</td> <td>Over Budget</td> <td>In Progress</td> <td>In Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">39</td> <td>Project 11</td> <td>Under Budget</td> <td>In Progress</td> <td>Out of Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">40</td> <td>Project 12</td> <td>At Budget</td> <td>In Progress</td> <td>Out of Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">41</td> <td>Project 13</td> <td>Under Budget</td> <td>Completed</td> <td>Out of Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">42</td> <td>Project 14</td> <td>At Budget</td> <td>Completed</td> <td>Out of Scope</td> <td> </td></tr> <tr style="HEIGHT: 18px"> <td style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">43</td> <td>Project 15</td> <td>Under Budget</td> <td>Delayed</td> <td>Out of Scope</td> <td> </td></tr></tbody></table>
Obviously, column K above would house the formula to give the overall status.

Any help that anyone can offer would be great. I've been banging my head against this one for a couple of days now...

 

[Derek Brown]

I would consider using concatenation with either VLOOKUP or INDEX/MATCH.
Keeping it simple to explain (you can re-arrange your matrix if you want to):
In your matrix add the following formula in F29:
=C29&D29&E29
and in G29:
=B29
In the spreadsheet add the following formula in K29:
=VLOOKUP(H29&I29&J29,matrixtable,2,FALSE)
where 'matrixtable' would be $F$29:$G$49 on the worksheet containing the matrix.
(Please note that I have not tested the above in a workbook, so it may contain typing errors.)

 

[stanleytheyak]

That worked like a champ! Thank you so much!