Formula Length Limitation

Hi all!


I have been struggling with a problem of overcoming formula length limitation. My problem is as follows:

I have 4 parameters with several values for each in "Scenario Control" sheet. First parameter represents 3 cost scenarios. Other three parameters define another 10 different cost scenarios of another segment of the whole system. So they give 30 possible combinations all together (hope I counted it right ).

I have each of those 3 and 10 cost scenarios calculated separately and have results of them written in another sheet.

Now I need to make a summary sheet, where just results from chosen combination of two scenarios from scenario group 1 + 2 will be depicted. And all has to be defined by user's selection of the combination of those four parameter values in the "Scenario Control" sheet.

Problem is that it could be done by 30 IF functions in the formula , but the limitation of the formula length is against me...

I wasn't able to find smarter way of doing it in order to bypass the limitation.

It's really urgent, I would be very grateful if somebody could help me!

Maybe by some VBA code...? I am not expert in this...

Thank you!

Perhaps you could use named formulas. Or simplify the formulas you have.