How to pull in status based on text string and number range

[lilvictorian]

Hello,

I have a table which includes statuses (red, yellow, green) which is based on two criterion: Stage and Days. The number of days for each of the statuses varies by the stage. I'm trying to find the easiest way to pull in the Status when I have the Stage and Days.

I've included two screenshots to illustrate:
1. Status Table
2. Tracking File (includes desired results)

Any help would be greatly appreciated!

 

[sxhall]

Hi Lilvictorian,

A big nested IF formula to determine the colour and some conditonal formatting on the cells to colour them...

=IF(G5="A",IF(H5<5,"Green",IF(H5>5,"Red","Yellow")),IF(G5="B",IF(H5<20,"Green",IF(H5>30,"Red","Yellow")),IF(G5="C",IF(H5<63,"Green",IF(H5>70,"Red","Yellow")),IF(G5="D",IF(H5<73,"Green",IF(H5>80,"Red","Yellow")),IF(G5="E",IF(H5<83,"Green",IF(H5>90,"Red","Yellow")),"")))))

I have put the above in to cell J5 in my sheet to check the results against your example. However are the criteria for green correct for stage C, D and E? Only they look as though they should be the lower limit for yellow. If not for stage C 68 would be true for both green and yellow as an example. Have taken a chance and put the lower value in to the formula for green.

Steven

 

[kweaver]

Agree with sxhall...and E-84 is <90 so that would be green, right?

 

[lilvictorian]

Steven - Thanks for you help and for catching my error. I was planning on doing it the way you suggested. I thought maybe there was an easier way using Offset, but this will work for me. Thank you again!