Using Indirect() function with a dynamically set named range

Hello,

[Edit Apr 27] - Example attached.

Let's say that in Sheet1 I have a local named range is that defined as 

• Name: Dynamic_Array
• Refers to:  =$A$1:INDEX($A$1:$E$1,3)

Range A1:E1 has number 1,2,3,4,5

In Sheet2, I have

• Cell A1 = Sheet1!
• Cell B1 = Dynamic_Arry
• Cell C1 = indirect(A1&B1)

the Indirect formula in C1 results in #ref error.  I understand as to why this is happening (Indirect formula cannot work with a named range that is defined with formulas because it requires a text as an argument)

Is there a workaround with this? 

I know I could create a UDF that returns the address of A1&B1 and do something like  = indirect ( UDF (A1&B1)) and make it work. But I am looking for a native solution to what UDF would do. 

1. I can't change the structure of the workbook (I cannot create a helper look-up table per se. I have thousands of these named ranges to reference)
2. I am looking for a native Excel solution to this. 
3. xlookup or vlookup does not work with the structure of my workbook