Alternative to INDIRECT (and OFFSET)

Hi,

I'm looking for an (non-volatile) alternative to the INDIRECT (and OFFSET function). I'm working with a price basis converter for a cash flow model, which converts a cash flow (starting in 2010) from e.g. N to R2017 taking corresponding year, a ccy cross vector (eg. EURUSD) and an inflation vector as input variables.

In the original formula the OFFSET and INDIRECT function is used to select the right [width]" of the inflation vector and ccy cross vector.

....OFFSET(INDIRECT("y_"&$H27);0;1;1;[width])...

Where the ["width"] is dynamic (eg. given by (2017-2010+1) and defined as (MAX(IF($G27="N";K$3;MID($G27;2;4)*1);IF($G28="N";K$3;MID($G28;2;4)*1))-MIN(IF($G27="N";K$3;MID($G27;2;4)*1);IF($G28="N";K$3;MID($G28;2;4)*1))))+1)

Where "y_"&$H27 is a named range of the inflation vector and $G27 $G28 are input cells

So far i'm trying to replace this by the INDEX function. Since the INDEX function doesn't have a [width] input, the reference range has to be changed dynamically. This could be done by using the ADDRESS function to define a range of the inflation vector, but hence it cannot be read by the INDEX formular without using INDIRECT..:

...INDEX(INDIRECT(INDEX(ADDRESS(MATCH("y_"&$H39;$H$1:$H$23;0);COLUMN(K$33))&":"&ADDRESS(MATCH("y_"&$H39;$H$1:$H$23;0);COLUMN(K$33)+(MID($G$33;2;4)-K$3-1));;));;)

So does anyone know an alternative to the INDIRECT function, so that I can define a dynamic range as reference in the INDEX formula.??

Such as..:

...INDEX(INDEX(ADDRESS(MATCH("y_"&$H39;$H$1:$H$23;0);COLUMN(K$33))&":"&ADDRESS(MATCH("y_"&$H39;$H$1:$H$23;0);COLUMN(K$33)+(MID($G$33;2;4)-K$3-1);;));;)...

The function has to be dynamic to respond to user-changes of the input variable - hence hardcoded values will not work...

Thank you!

You can also do this for the Named Range bit instead of the Indirect.
Though it can get cumbersome..

=CHOOSE(MATCH(H27,$A$1:$A$4,0),y_Apple, y_Pear, y_Banana, y_Peach)

Where H27 = say Pear, and A1:A4 = Apple Pear Banana Peach

Thank you for the replies!

@mikeTron
You're probably right!..

@Jonmo1
I guess it's still not possible to combine the width feature (=E1:INDEX(E1:IV1,[width]))) of the index function with a dynamic part to define a the range .."=E1:INDEX(E1:IV1..".., like =ADRESS(ROW(xx);Collumn(xx)):INDEX(ADRESS(ROW(xx);Collumn(xx))+x;[width]))...

I will attach a sample of the workbook in a minute

Hi

Attached is a short version of the original model.

Thank you.