FORECAST, in single cell, from 4 sheets?

[B.W.B.]

Hi to all!
Please, help. I need single cell forecast formula from four sheets. I already have concept(formula), but I don't know how to combine formula w/o helper columns X's Y's.

I'm stuck here: =FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),HOW TO INSERT HERE "XY" FROM ALL 4 RESERVOIR?)
Thank you for your help in advance.

https://1drv.ms/x/s!ArVOOVnFRGX4g-4E1j0CApEasV2CtA


Also asked on: http://www.ozgrid.com/forum/showthread.php?t=201300

[B.W.B.]

FORECAST(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);(INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1);1))INDEX(IF(D6="R-5";'R-5'!A4:B243;IF(D6="R-6";'R-6'!A4:B241;IF(D6="R-7";'R-7'!A4:B245;IF(D6="R-13";'R-13'!A4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);0);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1;1));(INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)))INDEX(IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));MATCH(INDEX(F6/VLOOKUP(E6;IF(D6="R-7";Factors!A3:B165;Factors!E3:F195);2;FALSE);1);IF(D6="R-5";'R-5'!B4:B243;IF(D6="R-6";'R-6'!B4:B241;IF(D6="R-7";'R-7'!B4:B245;IF(D6="R-13";'R-13'!B4:B289;""))));1)+1)))
HERE IS THE GARGANTULA, Where is there is : and (

My problem is, this jumbo crazy single cell formula is volatile, PLEASE HELP. Is it 'cause INDEX(IF part??
Thanks in advance!

[B.W.B.]

With helper cells(L6:M6 L7:M7), this gargantula isn't volatile. But, why?
=FORECAST(D6,L6:M6,L7:M7)

[B.W.B.]

There is no way! Eventually i ended up using helper cells A1:A4, it's such a Forecast formula, unable to handle array, in any way, Index, sumproduct....
=FORECAST(F6/VLOOKUP(E6,IF(D6="R-7",Factors!A3:B165,Factors!E3:F195),2,FALSE),A1:A2,A3:A4) it's working marvelously. Thanks to everyone!