IF and OR help. :(

[lostinexcel1776]

Hi there - I'm newer to using excel, (I know, I know), and I've hit a roadblock with understanding IF/OR statements. I've seen examples for what I'm trying to do, but no matter how many times I try to duplicate the statement, I can't make it work...

The old statement a spreadsheet I'm working with used is: =IF(OR(Status71 = "At Risk - Escalation Required", Status71 = "At Risk"), "Red", IF(Status71 = "In Progress - Behind Schedule", "Yellow", IF(OR(Status71 = "Scheduled", Status71 = "On Deck", Status71 = "In Progress - On Track", Status71 = "Redline - In Progress ", Status71 = "Submitted to IT", Status71 = "Moved to Production"), "Green", IF(OR(Status71 = "To Be Scheduled", Status71 = "Not Started"), "Blue", "Green"))))

This statement works fine with our old statuses, but now they've changed.

The different statuses I have need to correspond to a color in another column, but I need to change it to be:

"Not Started" = Blue
"Not Started – Behind Schedule" = Yellow
"In Process – On Track" = Green
"In Process – Behind Schedule" = Yellow
"Redline – In Process" = Green
"Submitted to IT" = Green
"Initial Coding – In Process & On Track" = Green
"Initial Coding Complete – Submitted to QA" = Green
"Initial Coding Compete – Available for Testing" = Green
"Testing – In Process" = Green
"Testing – Complete" = Green
"At Risk" = Red
"Complete" = Green
"N/A" = Green


If this is super easy for someone to figure out, I'd be forever grateful. Also, if there are any tricks to make these statements easier to setup in the first place, I'm all ears!

 

[AlphaFrog]

It may be easier to make a table on the sheet and use a VLookup formula.

You can use something like this.

Make sure the colors are in the same order as the statuses.

=INDEX({"Blue","Yellow","Green","Yellow","Green","Green","Green","Green","Green","Green","Green","Red","Green","Green"},
MATCH(Status71,{"Not Started","Not Started – Behind Schedule","In Process – On Track","In Process – Behind Schedule","Redline – In Process","Submitted to IT","Initial Coding – In Process & On Track","Initial Coding Complete – Submitted to QA","Initial Coding Compete – Available for Testing","Testing – In Process","Testing – Complete","At Risk","Complete","N/A"},0))

 

[AlphaFrog]

Another one that's a little shorter.


=LOOKUP(MATCH(Status71,{"Not Started","Not Started – Behind Schedule","In Process – Behind Schedule","At Risk","In Process – On Track","Redline – In Process","Submitted to IT","Initial Coding – In Process & On Track","Initial Coding Complete – Submitted to QA","Initial Coding Compete – Available for Testing","Testing – In Process","Testing – Complete","Complete","N/A"},0),{1,2,4,5},{"Blue","Yellow","Red","Green"})