Ramp Up formula without VBA

[FlashDota]

I have a task which I want to complete without using VBA. I want to multiply the value for each month with the corresponding months percentage down below. But Month 1 won't be the same for every person, since for Person 1 Starting Month is January, Person 2 Starting Month is February and Person 3 Starting Month is April. Reference the table below for desired results. What formula does this in the most straightforward way?

List	Starting month	Month 1	Month 2	Month 3		Desired results
Person 1	1/1/2024​	150​	250​	350​		150*10%	250*20%	350*30%
Person 2	2/1/2024​	290​	390​	490​		290*20%	390*30%	490*40%
Person 3	4/1/2024​	640​	340​	140​		640*40%	340*50%	140*60%
1/1/2024​	1/2/2024​	1/3/2024​	1/4/2024​	1/5/2024​	1/6/2024​	1/7/2024​	1/8/2024​	1/9/2024​	1/10/2024​	1/11/2024​	1/12/2024​
10%​	20%​	30%​	40%​	50%​	60%​	70%​	80%​	90%​	80%​	70%​	60%​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	List	Starting month	Month 1	Month 2	Month 3		Desired results
2	Person 1	01/01/2024	150	250	350		15	50	105
3	Person 2	01/02/2024	290	390	490		58	117	196
4	Person 3	01/04/2024	640	340	140		256	170	84
5
6
7
8
9	01/01/2024	01/02/2024	01/03/2024	01/04/2024	01/05/2024	01/06/2024	01/07/2024	01/08/2024	01/09/2024	01/10/2024	01/11/2024	01/12/2024
10	10%	20%	30%	40%	50%	60%	70%	80%	90%	80%	70%	60%
Sheet6

Cell Formulas
Range	Formula
G2:I4	G2	=C2:E2*INDEX($A$10:$L$10,XMATCH(B2,$A$9:$L$9)+{0,1,2})
Dynamic array formulas.

 

[FlashDota]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	List	Starting month	Month 1	Month 2	Month 3		Desired results
2	Person 1	01/01/2024	150	250	350		15	50	105
3	Person 2	01/02/2024	290	390	490		58	117	196
4	Person 3	01/04/2024	640	340	140		256	170	84
5
6
7
8
9	01/01/2024	01/02/2024	01/03/2024	01/04/2024	01/05/2024	01/06/2024	01/07/2024	01/08/2024	01/09/2024	01/10/2024	01/11/2024	01/12/2024
10	10%	20%	30%	40%	50%	60%	70%	80%	90%	80%	70%	60%
Sheet6

Cell Formulas
Range	Formula
G2:I4	G2	=C2:E2*INDEX($A$10:$L$10,XMATCH(B2,$A$9:$L$9)+{0,1,2})
Dynamic array formulas.

Thanks, this is a great formula. So if I was to add more months to it, I would just need to change the first range "C2:E2" and the numbers in bracket {0, 1, 2}.
Is there a way to make this part {0, 1, 2} also dynamic? Let's say I did this for 36 months I would need to wrote 0 to 35 manually

 

[Fluff]

For 36 months you could use Sequence(,36,0) instead of {0,1,2}

 

[FlashDota]

For 36 months you could use Sequence(,36,0) instead of {0,1,2}

I tried it but it's not working?

List

Starting month

Month 1

Month 2

Month 3

Month 4

Month 5

Month 6

Month 7

Month 8

Month 9

Month 10

Month 11

Month 12

Month 13

Month 14

Month 15

Month 16

Month 17

Month 18

Month 19

Month 20

Month 21

Month 22

Month 23

Month 24

Month 25

Month 26

Month 27

Month 28

Month 29

Month 30

Month 31

Month 32

Month 33

Month 34

Month 35

Month 36

Person 1

1/1/2024​

150​

250​

350​

150​

250​

350​

150​

250​

150​

250​

350​

350​

150​

250​

350​

150​

150​

250​

350​

250​

350​

150​

250​

350​

150​

250​

350​

150​

250​

350​

150​

250​

150​

250​

350​

350​

Person 2

2/1/2024​

290​

390​

490​

290​

390​

490​

290​

390​

290​

390​

490​

490​

290​

390​

490​

290​

290​

390​

490​

390​

490​

290​

390​

490​

290​

390​

490​

290​

390​

490​

290​

390​

290​

390​

490​

490​

Person 3

4/1/2024​

640​

340​

140​

640​

340​

140​

640​

340​

640​

340​

140​

140​

640​

340​

140​

640​

640​

340​

140​

340​

140​

640​

340​

140​

640​

340​

140​

640​

340​

140​

640​

340​

640​

340​

140​

140​

1/1/2024​

2/1/2024​

3/1/2024​

4/1/2024​

5/1/2024​

6/1/2024​

7/1/2024​

8/1/2024​

9/1/2024​

10/1/2024​

11/1/2024​

12/1/2024​

0.1​

0.2​

0.3​

0.4​

0.5​

0.6​

0.7​

0.8​

0.9​

0.8​

0.7​

0.6​

Desired results

Person 1

15​

50​

105​

60​

125​

210​

105​

200​

135​

200​

245​

210​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

Person 2

58​

117​

196​

145​

234​

343​

232​

351​

232​

273​

294​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

Person 3

256​

170​

84​

448​

272​

126​

512​

238​

384​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

#REF!​

 

[Fluff]

What is the formula?

 

[FlashDota]

What is the formula?

=C2:AL2*INDEX($A$10:$L$10,XMATCH(B2,$A$9:$L$9)+SEQUENCE(,36,0))

I also attached the excel cells which I tried it on, on my last comment

 

[Fluff]

You need to change the range inside the index/match, so it should be

=C2:AL2*INDEX($A$10:$AJ$10,XMATCH(B2,$A$9:$AJ$9)+SEQUENCE(,36,0))

 

[FlashDota]

You need to change the range inside the index/match, so it should be

=C2:AL2*INDEX($A$10:$AJ$10,XMATCH(B2,$A$9:$AJ$9)+SEQUENCE(,36,0))

Thanks for the answers

 

[Fluff]

You're welcome & thanks for the feedback.