Automatically update Prev Month's Values - Index & Match Formula?

[itsjules]

Hi All,

I need some help, I want to automatically update the previous months values each time the month changes with out going back in and updating it manually. I figured an Index and Match Formula would work, but something is wrong with my Match Formula.
I know the formula for Previous Month is ==TEXT(DATE(2017, IF(MONTH(NOW())-1>0, MONTH(NOW())-1,12),1),"mmmm")
AND i want to use that in the Match Function but it keeps coming up #N/A!

[TABLE="width: 1014"]
<tbody>[TR]
[TD][/TD]
[TD]January[/TD]
[TD]February[/TD]
[TD]March[/TD]
[TD]April[/TD]
[TD]May[/TD]
[TD]June[/TD]
[TD]July[/TD]
[TD]August[/TD]
[TD]September[/TD]
[TD]October[/TD]
[TD]November[/TD]
[TD]December[/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]174.33[/TD]
[TD]210.30[/TD]
[TD]235.09[/TD]
[TD]256.32[/TD]
[TD]297.73[/TD]
[TD]254.50[/TD]
[TD]395.61[/TD]
[TD]291.50[/TD]
[TD]337.35[/TD]
[TD]284.68[/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD]18.38[/TD]
[TD]15.45[/TD]
[TD]19.17[/TD]
[TD]14.47[/TD]
[TD]17.32[/TD]
[TD]15.18[/TD]
[TD]24.28[/TD]
[TD]19.50[/TD]
[TD]19.90[/TD]
[TD]18.21[/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD]Alison[/TD]
[TD]38.81[/TD]
[TD]31.25[/TD]
[TD]41.52[/TD]
[TD]47.42[/TD]
[TD]67.77[/TD]
[TD]32.95[/TD]
[TD]37.94[/TD]
[TD]35.86[/TD]
[TD]31.90[/TD]
[TD]47.53[/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD]Becca[/TD]
[TD]7.43[/TD]
[TD]3.80[/TD]
[TD]5.87[/TD]
[TD]7.21[/TD]
[TD]19.05[/TD]
[TD]6.64[/TD]
[TD]8.22[/TD]
[TD]4.68[/TD]
[TD]6.05[/TD]
[TD]7.79[/TD]
[TD]#DIV/0![/TD]
[TD]#DIV/0![/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD="colspan: 2"]Prev Month's Count[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Jim[/TD]
[TD]September[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Peter[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Alison[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Becca[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[Cross Terrier]

In B7 you can get the name of the previous month with: =INDEX($A$1:$M$5,1,MONTH(NOW()))
In C7 you can get the previous month's count with: =INDEX($A$1:$M$5,MATCH($A8,$A$1:$A$5,0),MONTH(NOW()))

These take advantage of the fact that the titles of the rows take up the first column so you don't need to worry about MONTH()-1. Both formulae can be copied down for all four names.

Oh, and neither will work for January!

 

[itsjules]

Thanks! but i would need it to work for January.. so instead of using the match index function, i calculated the Prev Month in a different cell and then used an HLookup Function to pull it up that way, seems to work! we'll find out tomorrow if it doesn't work!