Indirect equivalent for named arrays

[snv]

I can use the function INDIRECT ("myRange") to create a reference for
my named range.
However, If I use INDIRECT("myArray") to use as a reference to my
named array I get a #REF! error.
Is there a function equivalent to INDIRECT that I can use to reference
named arrays?

 

[T. Valko]

You can use CHOOSE but you're limited to 29 named references in versions of
Excel prior to Excel 2007.

MyArray1
MyArray2

=SUM(CHOOSE(MATCH("MyArray2",{"MyArray1","MyArray2"},0),MyArray1,MyArray2))

You can use cell refs for the MATCH arguments.

=SUM(CHOOSE(MATCH(A1,B1:B5,0),MyArray1,MyArray2))

INDIRECT returns the #REF! error when the named reference resolves to a
formula. For example:

MyArrayN refers to a dynamic named range like:

=OFFSET(Sheet1!$H$1,,,COUNT(Sheet1!$H:$H))

=SUM(INDIRECT(MyArray2)) = #REF!