Very tricky problem, can anyone help?

[excelos]

Hello!

=IF(AND(D15>=$J26,D15<$J26+7),$G26,IF(D15>$J26,INDEX($H$38:$H$136,COLUMNS($D$16:D16)),0))

If the date D15 is within the range AND(D15>=$J26,D15<$J26+7), then fill the cell with $G26.
If the date D15 is outside the above range, and it is lower than $J26, then fill the cell with 0.
If the date D15 is outside the above range, and it is greater than $J26, then fill the subsequent cells with the values from another range ($H$38:$H$136) one by one.

However, I cannot get right that last bit. I cannot find how to make the formula know when to start filling the values from the other range.
The COLUMNS($D$16:D16)) is not correct, but I don't know what to replace it with. It should start with 1 and grow to 2, 3, etc, but I don't know how the formula will remember that the previous value of it was 2, 1, etc.

Any idea?

It's so tricky!

Thanks

 

[excelos]

I am thinking to have another IF condition to check the previous value, but how can I make it remember that cell and start the COLUMNS counting?

Thanks!

 

[excelos]

I came up with this:

=IF(AND(D15>=$J26,D15<$J26+7),$G26,IF(D15>$J26,INDEX($H$38:$H$136,COLUMNS(INDEX($D16:$AN16,1,MATCH($G26,$D16:$AN16,1):D16),0),0))

But it says 'we found a problem with this formula'. I did try to enter it as array formula.

Any hint?