Index Matching Financial Data on Different Timelines

Brett Robinson

Our company works on projects that run on different timelines than our fiscal year. Our fiscal year is a calendar year but we may have a project whose base year runs from August 2019 to August 2020. However, we need to report financials on both timelines. I've been working on building a financial tracking sheet to pull the data on both timelines but have run into an issue using index/match to pull the data based on fiscal year. 

Each contract year (i.e. Base, OY1, OY2, etc.) has certain financials categories: target, budgets, actuals, and projected. The issue I'm having is getting the fiscal year (which reports on the same financial categories) to pull the right row (target, budgets, etc.) base on which fiscal year month (current the columns). I attached some screenshots of the set up since it's tough to explain. The only thing I can think of is flipping the Months to run vertically so the rows have unique identifications so that index match is not going to find the first row that matches. For example, right now if I want actuals for September 2018, it will actually pull actuals for September 2017. 

I don't want to flip the rows and the data will be copied from another system that reports the months horizontally. Thanks for any help/ideas in advance. 

Andrée Starå

Hi Brett,

I think we can use the new CONTAINS function. Can you share the formulas you're using? 

Have a fantastic weekend!

Best,

Andrée Starå

Workflow Consultant @ Get Done Consulting

Brett Robinson

Andree, 

 

Sorry for the delay. This would be the index match formula, but its currently cannot distinguish the Target/Budgets/Actuals rows for Base, OY1, OY2:

=INDEX($[Contract Year]$68:$[Partial End Month]$92, MATCH($[Contract Year]@row, $[Contract Year]$68:$[Contract Year]$92, 0), MATCH(NPS$5, $[Contract Year]$68:$[Partial End Month]$92, 0))

 

Thanks for the help!

Andrée Starå

No worries!

Can you maybe share the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@getdone.se)

Brett Robinson

I shared a simplified version of the sheet with you that just has the areas I'm talking about. Thanks for the help. 

Brett Robinson

Andree,

I just want to let you know I was able to build a nested if formula utilizing index/match to get this to work. Super long formula, but works for now until I think of some better way to do this.

Thanks for taking a look. 

Andrée Starå

Excellent!

Happy to help!