Offset, Indirect or other?

[Gregg Wolin]

I suspect that this requires a simple offset or indirect formula, but I'm struggling. I have a set of five, 36-month revenue scenarios. Each month of my analysis, one of these 36-month scenarios will commence. The attached illustrates what I am trying to achieve better than I could write it here.

Thanks in advance!

 

[Peter Bartholomew]

I have very little idea as to what is going on but this INDEX with implicit intersection appears to put the right numbers in the right boxes for the fields 1-4. It gives identical results if you prefer the simplicity of array formulas.

= IF( Mo>(field-1), INDEX( data, Mo - (field-1), type ), 0 )

 

[John Jairo V]

Hi, to both of you!

Another option could be:
[N7]: =IF(N$4,INDEX($D$7:$H$186,MATCH($M7,$B$7:$B$186,),N$4),)

or... (if dates are the same in both areas)
[N7]: =IF(N$4,INDEX($D$7:$H$186,ROWS(N$7:N7),N$4),)

Both formulas drag it down and right. Blessings!

 

[vletm]

Gregg Wolin
If You won't have 900 formulas then
... test this ... 'or other'
by pressing [ Do It ]-button.

Ps .I left 'org Sheet1' there, because some of my results are different (rows).
This solution can modify as everything else.

 

[Gregg Wolin]

I have very little idea as to what is going on but this INDEX with implicit intersection appears to put the right numbers in the right boxes for the fields 1-4. It gives identical results if you prefer the simplicity of array formulas.

= IF( Mo>(field-1), INDEX( data, Mo - (field-1), type ), 0 )

Perfect! Thanks so much.

 

[Gregg Wolin]

Gregg Wolin
If You won't have 900 formulas then
... test this ... 'or other'
by pressing [ Do It ]-button.

Ps .I left 'org Sheet1' there, because some of my results are different (rows).
This solution can modify as everything else.

This is a great alternative, thanks for the code!