Formula to find the End date value from a date range.

[ftk0811]

Hello, '

I have a set of data where few projects are enlisted and the amount of hours spent per project per week is captured. Based on this data, I need to find out the end date of the project.

The challenge here is that, the project can start at any week and there are some projects which are already closed or are yet to start. I need a Lookup/Index-Match kind of solution, but just can't figure out what it would be. So far I could only solve it for the projects that starts at the very beginning of the given date range using this formula:

=INDEX($C$1:$I$1,MATCH(TRUE,ISBLANK(C2:I2),0)-1).

Can somebody help me to come up with a formula that is applicable for all the scenarios?

The attached excel has both dataset and "desired" end result.

Many thanks in advance for taking time to look into this.

[JohnTopley]

In C13

=IFERROR(INDEX($C$2:$I$2,LOOKUP(2,1/(INDEX($C$3:$I$8,MATCH($A13,$A$3:$A$8,0),0)),(COLUMN($C$2:$I$2)))-2),$B13)