Index-Match with Offset or Sum

Deonvg

New Member
Joined
Feb 4, 2010
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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
ABCDEFGHIJWXYZAAABACADAEAFAGAHAIAJAKALAMANAOAPAQARASATAU
1
220212021Jan2021Feb2021Mar2021Apr2021May2021Jun2021Jul2021Aug2021Sep2021Oct2021Nov2021Dec
3AugPredictionPredictionPredictionPredictionPredictionPredictionPredictionPredictionPredictionPredictionPredictionPrediction
4DataMonthPrediction
5MayJunJul20202021Aug - DecJanFebMarAprMayJunJulAugSepOctNovDecJanFebMarAprMayJunJulAugSepOctNovDec
628/01/202128/02/202128/03/202128/04/202128/05/202128/06/202128/07/202128/08/202128/09/202128/10/202128/11/202128/12/202128/01/202128/02/202128/03/202128/04/202128/05/202128/06/202128/07/202128/08/202128/09/202128/10/202128/11/202128/12/2021
7RmRmRmRmRmRm
8Revenue14886685894,4614,409551Revenue1597866564637488668589      547551544537529522
9Revenue24176604451,9523,341479Revenue2415374530500417660445      553479688527534743
10Revenue35314815023,6763,655510Revenue3552507549534531481502      526510509507506504
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
14How do I add a OFFSET or SUM to this formula to index Aug but add Sep-Dec to show 4 months
15If data changes to Aug for month then prediction must change to Sep-Dec
16
data
Cell Formulas
RangeFormula
AI2:AT2AI2=CurrYear&AI5
H2,G5H2=CurrYear
H3H3=FollowMnth
C5C5=PrPriorMnth
D5D5=PriorMnth
E5E5=CurrMonth
F5F5=PrYear
H5H5=FollowMnth&" - Dec"
W6:AH6W6=EDATE(K6,12)
AI6:AT6AI6=W6
AI8:AT10AI8=IF(X8="",FORECAST.ETS(AI$6,$K8:V8,$K$6:V$6,1,1),"")
H8:H10H8=INDEX($AI:$AT,MATCH($B8,$J:$J,0),MATCH(H$2&H$3,$AI$2:$AT$2,0))
J8:J10J8=B8
Named Ranges
NameRefers ToCells
CurrMonth='Ctrl Sheet'!$D$15E5:E6
CurrYear='Ctrl Sheet'!$D$20G5:G6, H2, AI2:AT2
FollowMnth='Ctrl Sheet'!$D$16H3, H5:H6
PriorMnth='Ctrl Sheet'!$D$17D5:D6
PrPriorMnth='Ctrl Sheet'!$D$18C5:C6
PrYear='Ctrl Sheet'!$D$21F5:F6
 

Attachments

  • XLS index-match.png
    XLS index-match.png
    43.9 KB · Views: 0

Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,574
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Feb 4, 2010
Messages
23
Office Version
  1. 365
Platform
  1. Windows
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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,574
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=SUM(FILTER(FILTER($AI$8:$AT$15,$J$8:$J$15=B8),$AI$6:$AT$6>=DATE($H$2,MONTH($H$3&"1"),28)))
 
Solution

Deonvg

New Member
Joined
Feb 4, 2010
Messages
23
Office Version
  1. 365
Platform
  1. Windows
Thanks for that, how about
Excel Formula:
=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(y). Will go through logic to make sure I understand how it works, as it's powerful. Really appreciate.
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
65,574
Office Version
  1. 365
Platform
  1. Windows
You're welcome & thanks for the feedback.
 

Forum statistics

Threads
1,147,452
Messages
5,741,206
Members
423,648
Latest member
steel1968

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top