Budget assistance - dependent on dates - index, match, offset?

[shakethingsup]

Happy thanksgiving and Columbus day!

Not sure if I'm thinking about this in the right way or if this is even efficient. I'm facing a couple of problems and can't construct the right formula. I feel it has to do with index, match, offset?

I'm thinking about this problem in this way as I know one of the questions I will be asked is "what if we change the timing of this budget line and the corresponding budget lines?" I will have to continuously cut/paste/change/shift values in the entire file - and there are many dependent/gating rows to change and I know I will miss one eventually. If we had a software it might be easier. Anyone else faced with this?

E.g. line 1 should start March, Line 2 and 3 are dependent on line 1 and would I would simply use a date formula (T+3, T+1, etc)

File Construct:

1. rows 2-4 - inputs of budget line items with start date and then corresponding $.
2. rows 8-10 - is supposed to be the output displayed monthly

The formula I used and the output is:
=IFERROR(INDEX($D$2:$I$6,MATCH($A8,$A$2:$A$6,0),MATCH(1,(D$7=$B$2:$B$6)*($A8=$A$2:$A$6),0)),0)

When I use this formula, it worked for line 1 except I didn't know how to pull in subsequent values. It clearly didn't work for line 2 and 3 as it skipped the first few values.

A

B

C

D

E

F

G

H

I

J

K

L

1

start

end

2

Line 1

31-Mar-23​

30-Jun-23​

4​

10​

15​

20​

30​

40​

3

line 2

30-Jun-23​

30-Sep-23​

17​

50​

300​

500​

4

line 3

30-Apr-23​

31-Jul-23​

7​

8​

9​

10​

11​

12​

5

6

7

Jan-23​

Feb-23​

Mar-23​

Apr-23​

May-23​

Jun-23​

Jul-23​

Aug-23​

Sep-23​

8

line 1

0​

0​

4​

0​

0​

0​

0​

0​

0​

9

line 2

0​

0​

0​

0​

0​

50​

0​

0​

0​

10

line 3

0​

0​

0​

9​

0​

0​

0​

0​

0​

But the ideal output should pull in line 1 at the right time and then the corresponding values and for all other lines:

D

E

F

G

H

I

J

K

L

Jan-23​

Feb-23​

Mar-23​

Apr-23​

May-23​

Jun-23​

Jul-23​

Aug-23​

Sep-23​

8

line 1

0​

0​

4​

10​

15​

20​

30​

40​

9

line 2

0​

0​

0​

0​

0​

17​

50​

300​

500​

10

line 3

0​

0​

0​

7​

8​

9​

10​

11​

12​

Anyone can point me to a reference post/website?

 

[shakethingsup]

hello - am I allowed to bump?