Match Index? How to split a saving value across 12 months based on month of saving realisation and other controls


New Member
Oct 25, 2019

I am trying to create a savings log which would enable us to detail down the savings we generate as a business.

Essentially the main issue I am facing is that I can dedicate fields to detail the saving total along with a date that can be used to identify a realisation month for the saving but i cannot think of a way to apportion the savings down depending on type and across monthly ranges.

This is where things have become a little tricky as there are a few controls that are needed.

1. Savings can be designated as either a one off or annualised in column P. So in essence the total value is either captured in a single month based on the realisation date column E, or the saving total split over 12 values and 12 months from the realisation date. Saving total can be found in column R.

2. Savings can be entered but can only be accounted in the month columns U to AR once approved and the "yes" option selected from the drop down in column T.

I toyed with If statements and sum ifs based on approval status in column T and I can get the value to display or not in a single column. I stumbled across Index and Match functions but not sure if this is the solution or how to get this working.

What I am lost with here is how to get the value to appear in the correct month in the range of Columns U to AR, and then how to get this to either lump the value into one month if designated one off in column P or if annualised is selected to start at the realisation month and then display 12 consecutive values for the period of 12 months starting from the realisation month.

I've attached a dropbox link below to a copy of the workbook with a typed example of what i'm hoping to achieve. Price Increase & Savings Log ME.xlsx?dl=0

Any help is appreciated.


Some videos you may like

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Watch MrExcel Video

Forum statistics

Latest member

This Week's Hot Topics