Help with Formula / INDEX to show previous month's data

Jenn P

Hoping to get a little help with a formula / Index set up. I found a scenario/question similar to what I am needing, but I can't seem to get it to work. The scenario I found provides =INDEX(January@row:December@row, 1, MONTH(DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - 1))

Think it's the INDEX portion of the formula that is causing my trouble (Haven't worked with INDEX or VLookup before).

The columns on my stats sheet are set by our fiscal year Jul 20, Aug 20, etc . The formula needs to pull the previous month's data to be displayed on a dashboard (Metric Widget) and update automatically each month. So when looking at the dashboard this month (Feb), it will show January's data.

Could someone show how this would need to be set up to pull the data correctly?

Any assistance would be greatly appreciated!!

Mark Cronk

Assume your widget sheet has the measure and last month's value columns. Place this in the last month value column:

=VLOOKUP(Measure@row, {measure range}, IF(month(today())>=8, Month(today())-6, month(today())+6)) where the {measure range} is the table you attached with the measure in column 1 and July 20 to Jun 21 are columns 2 - 13.

Work?

Mark