How to Return a value between two dates for cashflow modelling

I am working on a cash flow forecast for a small business and want to model when invoices are sent and went those same invoices are paid on a monthly basis, but tracked weekly (will added quarterly, semi-annual and annual invoices later). I want to be able to change when the invoices are sent and how long the invoice are outstanding before paid; for sensitivity analysis. This will help forecast when bank loans will be drawn

The attached spreadsheet shows the values I want add. in Cell C35, G35, H36, M36.....

Is this possible?

Client Client A
Frequency Quarterly
Invoice Cycle Every 28
Days to Collect 35
Invoice 1 Sent Date Wednesday, January 9, 2019 Invoice Sent
Invoice 2 Sent Date Wednesday, February 6, 2019 Invoice Sent
Invoice 3 Sent Date Wednesday, March 6, 2019 Invoice Sent
Invoice 4 Sent Date Wednesday, April 3, 2019 Invoice Sent
Invoice 5 Sent Date Wednesday, May 1, 2019 Invoice Sent
Invoice 6 Sent Date Wednesday, June 5, 2019 Invoice Sent
Invoice 7 Sent Date Wednesday, July 3, 2019 Invoice Sent
Invoice 8 Sent Date Wednesday, August 7, 2019 Invoice Sent
Invoice 9 Sent Date Wednesday, September 4, 2019 Invoice Sent
Invoice 10 Sent Date Wednesday, October 2, 2019 Invoice Sent
Invoice 11 Sent Date Wednesday, November 6, 2019 Invoice Sent
Invoice 12 Sent Date Wednesday, December 4, 2019 Invoice Sent

Date Paid Invoice 1 Wednesday, February 13, 2019 3,000.00
Date Paid Invoice 2 Wednesday, March 20, 2019 3,000.00
Date Paid Invoice 3 Wednesday, April 24, 2019 3,000.00
Date Paid Invoice 4 Wednesday, May 29, 2019 3,000.00
Date Paid Invoice 5 Wednesday, July 3, 2019 3,000.00
Date Paid Invoice 6 Wednesday, August 7, 2019 3,000.00
Date Paid Invoice 7 Wednesday, September 11, 2019 3,000.00
Date Paid Invoice 8 Wednesday, October 16, 2019 3,000.00
Date Paid Invoice 9 Wednesday, November 20, 2019 3,000.00
Date Paid Invoice 10 Wednesday, December 25, 2019 3,000.00
Date Paid Invoice 11 Wednesday, January 29, 2020 3,000.00
Date Paid Invoice 12 Wednesday, March 4, 2020 3,000.00


Week 1 Week 2 Week 3 Week 4 Week 5 Week 6 Week 7 Week 8 Week 9 Week 10
Start Tuesday, January 1, 2019 2019-01-07 2019-01-14 2019-01-21 2019-01-28 2019-02-04 2019-02-11 2019-02-18 2019-02-25 2019-03-04
End Friday, January 4, 2019 2019-01-11 2019-01-18 2019-01-25 2019-02-01 2019-02-08 2019-02-15 2019-02-22 2019-03-01 2019-03-08
Client A-Invoice Sent Date Invoice Sent Invoice Sent ????
Client A-Invoice Paid Date 3000

Can you show some sample answers? (or, if you did, explain what you did/want?)

Try: in b19: =IFERROR(IFERROR(VLOOKUP(B$17,$B$5:$C$8,2,0),VLOOKUP(B$18,$B$5:$C$8,2,0)),"")
in C19: =IFERROR(IFERROR(VLOOKUP(B$17,$B$10:$C$13,2,0),VLOOKUP(B$18,$B$10:$C$13,2,0)),"")

Glad to help. If that took care of your problem, please mark the thread as Solved from Thread Tools.

Actually. The answer worked bc the dates matched perfectly with the weeks. I added a new spreadsheet that has the date invoice sent and paid within the weekly date range. Can you solve this added complexity? Sorry for the boomerang

This should work:
=IFERROR(LOOKUP(2,1/($B$5:$B$16>=B33)/($B$5:$B$16<=B34),$C$5:$C$16),"")
=IFERROR(LOOKUP(2,1/($B$18:$B$29>=B33)/($B$18:$B$29<=B34),$C$18:$C$29),"")

Great again. THanks.