How to offset an amount to another row below, based on dates?

Hello everyone.

I have a table that should inform the balance of an account.
Any row would look like this:
Period: 1
Period start: Apr-5-2021
Period end: Apr-11-2021
Estimate: $25,000
Payment: I want those 25,000 from above to be offset to another row
Balance: whatever

There is a cell outside the table where I specify the number of days after which the estimate will be paid. I need the value from that cell to be compared to the columns Period start and Period end, it's a range of dates.
I have this formula which is how I expected to solve it, but it does something different, it brings me the header of the table and it skips some values.
=OFFSET(E11,ROW(D11)-ROW(D$11)-MATCH(D11+C$4,$D$11:$D$24,1),0)

Attached is a sample file if my explanation is not clear.

Thank you very much.

Array formula in F11

Please Login or Register  to view this content.

Confirmed with Ctrl+Shift+Enter then copied down

Thank you very much, Soledad. That works very well.
Mind sharing your knowledge a bit? why does the E:E works and not a fixed range like $E$11:$E$24?, how does the SMALL function with the COUNT is working here? I thought that function was useful only for ranking data?
Thank you, again.

Ok, can this be done without an array formula?

Thank you AligW, I'm using Excel 2013, I have to update that field in my profile. The problem I see is that the person who uses the workbook is likely to check the formulas out of curiosity, and they're also likely to freak out if they see their cell change. Also, I had a very reliable alternative formula that seemed to work until I noticed a few problems. The formula was this:

Please Login or Register  to view this content.

It worked most of the times, I was using a helper column with the payment dates, but since it was only using the "period end" column to match them, it started to return the wrong result. It worked like 90% of the times, so I supposed a non-array formula could do the job if better designed.

As for the full column range, I made changes to the formula like this:

Please Login or Register  to view this content.

And now it allows me to have a blank cell in period 0 and with the exact range. Still an array though.

Thank you again, AligW, for your time.

I think I broke something so I will re-evaluate it with that change. Thank you again.

Hi, Glenn. Thank you for your time.

I'm attaching the file with the formula applied, just noticed none of the formulas actually do it right because when the payment term is 0, it shouldn't offset any payment, but from day 1 onwards, it should according to the date ranges in columns C and D. Also added another worksheet with my formula which doesn't work in semi-monthly periods. In the end, as long as the payment date is between columns C and D, regardless of the type of period, the invoice total should appear in that row.

Thank you very much for that formula, bebo021999. Your formula works most of the times, but similar to the index-match method, since it only accounts for the "period end", it returns duplicate invoice totals when dates are semi-monthly, that is:
1st to 14th day of the month
15th to last day of the month

You might be surprised, but when the payment term is 15 days, you can see how it matches the wrong number. The same happens to other payment terms like 16, 17, and when the periods are arranged in a monthly way, it does something similar. I'm not sure why this is, as I'm yet to fully understand your approach, but in the index-match approach, it had something to do with trying to match to the nearest lowest value instead of also accounting for the period start.

The invoice total is supposed to be always the same as the total cost.

I'm attaching a file with your formula and with the scenario I just tried to explain above. Please, try to play with the spinner control and see how it works well for most of the payment terms, but then it fails with 15, 16, 17 and others. You may also notice the invoice totals are also different in this scenario, the reason is because the periods are longer and thus they cost more, but in the end, they amount to the same total cost.

I guess if there's no other choice, an array formula should be used.

Thank you again, bebo021999. What I meant by "ignore blanks" is that in my actual dataset, the dates in columns C and D (period start and period end) are calculated via a formula, and that formula produces a "" for all of the dates that won't be used. These blanks produce a #VALUE! error in the SUMPRODUCT formula. This part of the formula

Please Login or Register  to view this content.

Is not ignoring the blanks, the SUMPRODUCT is still using them and produces the error.

I tried this, but it didn't work.

=SUMPRODUCT(--($D$12:$D$26+$C$4>=C12),--($D$12:$D$26+$C$4<=D12),($E$12:$E$26))

That's PERFECT.

I learned so much from your examples, bebo021999. I was able to apply it to my actual data set and it's killing it.

The formula in the first post by Soledad was way outside my comfort zone but I also got to learn a lot from it. Tons of strategies with this little exercise. So thank you everyone, AliW, Glenn, each and all inputs were highly appreciated and educational.

Have a great day.

-Edgar