lookup

[glen.e.mettler]

the Max() function will allow me to capture the maximum value in a
column of data.
Is there a similar function for text? Example: I have a Projects
Status file that contains varioius data about the project and a color
code to provide a "stop light" indication of status.
Project ABC consists of 4 ecp that are variously G(reen), Y(ellow),
R(ed). At the ABC level I want to automatically show the worst level
of status - in this case R, because ecp3 is R. If ecp3 goes to G, then
the rollup value would be Y because ecp2 is Y etc.

Project Status
ABC R
ecp1 G
ecp2 Y
ecp3 R
ecp4 G

I know that I could convert the values to 1,2,3 for G,Y,R and fix the
problem. Howeverr, I need to show the G,Y,R in the cell for printing
in B/W (and because that's the way the customer wants it).

I can also add another column, insert the numbers and do a vlookup with
Max() to fill the cell.

I was looking for a simpler approach without adding columns and lookup
etc. Is there a function I can use to do this?



Glen

 

[Kevin]

Glen, I'd use a vlookup like this:
=IF(ISNA(VLOOKUP("R",$B$8:$B$12,1,FALSE))=FALSE,"R",IF(ISNA(VLOOKUP("Y",$B$8:$B$12,1,FALSE))=FALSE,"Y","G"))

Since there's only 3 options, it will filter through your column until it
"hits" the lowest tiered letter.

Hope this helps.

Kevin