Index-Match with Offset or Sum

[Deonvg]

Hi,

I have a index and match formula based on sample (attached/below). I would however like to either add a offset or sum to the formula so that it automatically adds the "remaining" months of the year to the prediction column.

i.e if latest month changes to Aug then the index & match plus offset/sum must automatically add the prediction from Sep to Dec instead of Aug to Dec.

your help will be appreciated.

'=INDEX($AI:$AT,MATCH($B8,$J:$J,0),MATCH(H$2&H$3,$AI$2:$AT$2,0))

Index-Match-Offset-Sum.xlsx
	A	B	C	D	E	F	G	H	I	J													W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG	AH	AI	AJ	AK	AL	AM	AN	AO	AP	AQ	AR	AS	AT	AU
1
2								2021																											2021Jan	2021Feb	2021Mar	2021Apr	2021May	2021Jun	2021Jul	2021Aug	2021Sep	2021Oct	2021Nov	2021Dec
3								Aug																											Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction	Prediction
4		Data	Month					Prediction
5			May	Jun	Jul	2020	2021	Aug - Dec															Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
6																							28/01/2021	28/02/2021	28/03/2021	28/04/2021	28/05/2021	28/06/2021	28/07/2021	28/08/2021	28/09/2021	28/10/2021	28/11/2021	28/12/2021	28/01/2021	28/02/2021	28/03/2021	28/04/2021	28/05/2021	28/06/2021	28/07/2021	28/08/2021	28/09/2021	28/10/2021	28/11/2021	28/12/2021
7			Rm	Rm	Rm	Rm	Rm	Rm
8		Revenue1	488	668	589	4,461	4,409	551		Revenue1													597	866	564	637	488	668	589												547	551	544	537	529	522
9		Revenue2	417	660	445	1,952	3,341	479		Revenue2													415	374	530	500	417	660	445												553	479	688	527	534	743
10		Revenue3	531	481	502	3,676	3,655	510		Revenue3													552	507	549	534	531	481	502												526	510	509	507	506	504
11
12								=INDEX($AI:$AT,MATCH($B8,$J:$J,0),MATCH(H$2&H$3,$AI$2:$AT$2,0))																																	=IF(AD8="",FORECAST.ETS(AO$6,$K8:AB8,$K$6:AB$6,1,1),"")
13
14								How do I add a OFFSET or SUM to this formula to index Aug but add Sep-Dec to show 4 months
15								If data changes to Aug for month then prediction must change to Sep-Dec
16
data

Cell Formulas
Range	Formula
AI2:AT2	AI2	=CurrYear&AI5
H2,G5	H2	=CurrYear
H3	H3	=FollowMnth
C5	C5	=PrPriorMnth
D5	D5	=PriorMnth
E5	E5	=CurrMonth
F5	F5	=PrYear
H5	H5	=FollowMnth&" - Dec"
W6:AH6	W6	=EDATE(K6,12)
AI6:AT6	AI6	=W6
AI8:AT10	AI8	=IF(X8="",FORECAST.ETS(AI$6,$K8:V8,$K$6:V$6,1,1),"")
H8:H10	H8	=INDEX($AI:$AT,MATCH($B8,$J:$J,0),MATCH(H$2&H$3,$AI$2:$AT$2,0))
J8:J10	J8	=B8

Named Ranges
Name	Refers To	Cells
CurrMonth	='Ctrl Sheet'!$D$15	E5:E6
CurrYear	='Ctrl Sheet'!$D$20	G5:G6, H2, AI2:AT2
FollowMnth	='Ctrl Sheet'!$D$16	H3, H5:H6
PriorMnth	='Ctrl Sheet'!$D$17	D5:D6
PrPriorMnth	='Ctrl Sheet'!$D$18	C5:C6
PrYear	='Ctrl Sheet'!$D$21	F5:F6

 

[Fluff]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[Deonvg]

What version of Excel are you using?

I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Hi Fluff,

Thanks and apologies have updated profile - using MSO 365 (16.0.13801.....), Windows

 

[Fluff]

Thanks for that, how about

=SUM(FILTER(FILTER($AI$8:$AT$15,$J$8:$J$15=B8),$AI$6:$AT$6>=DATE($H$2,MONTH($H$3&"1"),28)))

 

[Deonvg]

Thanks for that, how about

=SUM(FILTER(FILTER($AI$8:$AT$15,$J$8:$J$15=B8),$AI$6:$AT$6>=DATE($H$2,MONTH($H$3&"1"),28)))

Thank you, that is working perfect, changed the month in H3 and magic. Will go through logic to make sure I understand how it works, as it's powerful. Really appreciate.

 

[Fluff]

You're welcome & thanks for the feedback.