Vlookups ? Creating a traffic light matrix using

[masterelaichi]

Hi,

I am trying to create a traffic light kind of a report using excel. There are three different attributes based on which the performance of a project is assessed - Cost, Time, and Quality. These attributes can take one of three Status (R)ed, (A)mber, or (G)reen. What I am trying to create is a column called Overall Status which takes the status of the highest value of the other three columns i.e R>A>G

The table below is what I am trying to arrive at
[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Project[/TD]
[TD]cost[/TD]
[TD]time[/TD]
[TD]quality[/TD]
[TD]Overall Status[/TD]
[/TR]
[TR]
[TD]Project1[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]R[/TD]
[TD]R[/TD]
[/TR]
[TR]
[TD]Project2[/TD]
[TD]G[/TD]
[TD]G[/TD]
[TD]A[/TD]
[TD]A[/TD]
[/TR]
[TR]
[TD]Project3[/TD]
[TD]R[/TD]
[TD]A[/TD]
[TD]G[/TD]
[TD]R[/TD]
[/TR]
</tbody>[/TABLE]



I tried to give a score to each value and tried using vlookup, but couldn't figure it out

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]Status[/TD]
[TD]Value[/TD]
[/TR]
[TR]
[TD]R[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]G[/TD]
[TD]-1[/TD]
[/TR]
</tbody>[/TABLE]


I also want to include a check to see if one of the values in the column is something other than R, A,or G and return a message if true. Eg- Overall status is Blank indicates one of the attributes has a missing value

Any help will be appreciated. Thanks!

 

[FDibbins]

Im sure someone will give youy a better way, but this works, using helper columns *that you can hide if needed)
[Table="width:, class:grid"][tr][td] [/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][td]

F​

[/td][td]

G​

[/td][td]

H​

[/td][td]

I​

[/td][td]

J​

[/td][/tr]
[tr][td]

1​

[/td][td]Project[/td][td]cost[/td][td]time[/td][td]quality[/td][td]Overall Status[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

2​

[/td][td]Project1[/td][td]A[/td][td]G[/td][td]R[/td][td]R[/td][td][/td][td]

0​

[/td][td]

-1​

[/td][td]

1​

[/td][td]

0​

[/td][/tr]

[tr][td]

3​

[/td][td]Project2[/td][td]G[/td][td]G[/td][td]A[/td][td]A[/td][td][/td][td]

-1​

[/td][td]

-1​

[/td][td]

0​

[/td][td]

-2​

[/td][/tr]

[tr][td]

4​

[/td][td]Project3[/td][td]R[/td][td]A[/td][td]G[/td][td]R[/td][td][/td][td]

1​

[/td][td]

0​

[/td][td]

-1​

[/td][td]

0​

[/td][/tr]

[tr][td]

5​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

6​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

7​

[/td][td]Status[/td][td]Value[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

8​

[/td][td]R[/td][td]1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

9​

[/td][td]A[/td][td]0[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td]

10​

[/td][td]G[/td][td]-1[/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]
G2=VLOOKUP(B2,$A$8:$B$10,2,0)
copied across and down as needed
J2=SUM(G2:I2)
copied down.

You could, of course, combine all 3 vlookups into 1 formula and sum them...
=VLOOKUP(B2,$A$8:$B$10,2,0)+VLOOKUP(C2,$A$8:$B$10,2,0)+VLOOKUP(D2,$A$8:$B$10,2,0)

 

[masterelaichi]

Thanks FDibbins

I tried using If statements with countifs and it kind of managed to do it. I wasn't able to code in the error messages

 

[lrobbo314]

Here is an option. It's an array formula so use Ctrl+Shift+Enter when entering in the formula.

=INDEX({"A","G","R"},MATCH(MAX(LOOKUP(A1:C1,{"A","G","R"},{0,-1,1})),{0,-1,1},0))

 

[lrobbo314]

Here's a way to catch an error if one of the values isn't G, A, or R

=IF(ISERROR(SUMPRODUCT(MATCH(A1:C1,{"G","A","R"},0))),"Error",INDEX({"G","A","R"},MATCH(MAX(LOOKUP(A1:C1,{"G","A","R"},{-3,-2,-1})),{-3,-2,-1},0)))