Multiple Criteria, multi-layered formula, for both sheet and conditional formatting.

Hi folks,

Hoping someone can help with a problem I’ve been given. I’ve put together a table that allows us to track the stage gates that our various projects are expected to reach in the coming months, and what their BRAG (Blue, Red, Amber, Green) status is.

The problem with the current layout is that it requires multiple lines per project, and I’ve been asked to reduce this to a single line per project.

I suspect that this will require a multi-layered IF formula, with some form of lookup integrated into it, but I can't figure out how to make this work. Also, I should make clear that I am currently working with Excel 2016, however my company managed to purchase a version that does not carry the IFS function.

On that note, what I would like to do is two-fold:

1. Use a formula that can read the dates in C1:F1, based on the project under Column B identify the corresponding stage gates per respective project in the “Stage Gates – Breakdown” tab, and represent these under the corresponding column through the use of respective symbols show in the "Stage Gates - Symbol" tab;
2. Use a formula that would then allow each Stage Gate’s respective BRAG status to apply the appropriate colour / Conditional Format, as identified in the “Stage Gates – Breakdown” tab.

Additional issue - to complicate matters (even further!) you will note that some projects have Stage Gates occurring in the same month (see Project X2 highlighted in "Stage Gates - Breakdown" tab). The idea is that in instances like these, both corresponding symbols would be shown in the same cell.

I believe this last point would make applying the conditional formatting impossible, in which case I would prefer to ignore the conditional formatting altogether.

Hope this is all clear, and many thanks in advance for all your help on this! Looking forward to hearing from you.

Kind regards,
G