Hello!

Please see attached my sample data from a much larger dataset. I've been battling with this all day today(!) so really hoping someone will be able to help me.

I need a formula that will look at the dates for each programme level (column C and D) and enter the level code from column B into the correct month in the table. I've added some helper cell dates above the month table.

My finished table will have level codes against the relevant months for each project.

Example 1 - PROG1 will show 'L1' in May and June 2024 as this is when the dates are for this level.

Example 2 - PROG2 will have 'L6' in June, July, August and September 2025 as this is when the dates run from-to.

So far I have tried using INDEX MATCH with nested IF and AND formulas to say IF(AND(C3>=G2,C3<=G3) so that it's looking within the date ranges, but so far I'm not having any luck. I'm also conscious that formula only looks at the start date, so I need to include the finish date as well.

Eventually I would also like to add conditional formatting to colour code the cells based on the key at the bottom, but I'm more concerned with getting the summary table ready for now and wasn't sure if that needed to be in a different post.

Please let me know if I need to provide any further detail - it's been harder to explain than I thought!

Yours despairingly, XL23

Formula for looking up Level against Month.xlsx