Sum Dynamic Range Based on Cell Address Result from an INDEX MATCH array formula?

[CBICA]

I have a formula that I use to dynamically find the value of a cell based on a two row lookup.
It's a P&L, and you can imagine something like:
2018 2018 2018 2018
Q1 Q2 Q3 Q4
#A #B #C #D

Except for 5 years, the #A etc represents a number (revenue, cogs, op inc, whatever). I want to sum an arbitrary four quarters based on a target quarter.

To try to make the formula simple, I'm hardcoding the lookup row, hence the bold '1', but could easily add another MATCH in there to find the target row to return. This works and gives me the target result based on a Year and Quarter lookup:
{=INDEX('P&L'!$A$6:$AB$6,1,MATCH(YEAR(INPUT!$J$4)&INPUT!$J$5,'P&L'!$A$2:$X$2&'P&L'!$A$1:$X$1,0))}

However, I want to then SUM the previous 3 cells to the target cell returned from the formula above.
I thought something with CELL(addr or SUM(OFFSET(the formula above),0,-3), but that just returned an error.

Is there a way to sum the four cells based on the target cell returned from the indirect array formula?

 

[CBICA]

Actually, probably happens a lot here ... I went back and tried a few combinations, building it piecemeal, and I think I got it to work.

First made sure I was referencing the right cell, set up a dummy cell and used =CELL('addr',[formula from above]) ... which worked.

Then I used Indirect to unwind and get the value of the return cell from the formula above.

Then I did =SUM(INDIRECT(dummycell),OFFSET(INDIRECT(dummycell),0,-3,1,3))

And it so far checks out. Wow.

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(OFFSET(INDEX('P&L'!$A$6:$AB$6,1,MATCH(YEAR(INPUT!$J$4)&INPUT!$J$5,'P&L'!$A$2:$X$2&'P&L'!$A$1:$X$1,0)),0,-3,1,3))

will also work.
​