CODEWITHSUNDEEP
excel
jesh
jesh

Reputation: 29

Is it possible to point to array of named ranges with INDEX reference form?

I'm keeping data in several equally sized arrays saved as named ranges Arr1, Arr2, Arr3 etc. I want to look up things from any these arrays with INDEX or CHOOSE wrapped inside another INDEX e.g. by going INDEX(INDEX((Arr1;Arr2;Arr3);;MATCH...);2;2)

As my list of arrays grows large and I need to apply my INDEX/CHOOSE-MATCH many places, I'd like to avoid repetition and store the names of my named ranges in another array or table, let's call it Arr_list.

Is it possible to substitute the reference part, i.e. '(Arr1;Arr2;Arr3)' of an INDEX formula with a call to the Arr_list array?

Upvotes: 0

Views: 60

Answers (2)

MacroMarc
MacroMarc

Reputation: 3324

A way to do this without the INDIRECT function(which is volatile, and may slow down workbook with large ranges) is to make your Arr_List into one array using the colon Range operator, or the comma union operator.

I show an example of it below with the Master named ranges themselves as named ranges.

The Names manager

You may form a Union like so:

=Name,Age,Sales,Gender OR

=name:Age:Sales:Gender

The comma/Union way is referenced with the 2nd form of INDEX which takes a reference, and then gets the area as parameter like:

=INDEX(Master2, 2, 1, 4) to get Gender.

The Colon/Range way is referenced by the 1st form of Index:

=INDEX(Master, 2, 4)

Upvotes: 0

BigBen
BigBen

Reputation: 50162

Wrap the INDEX(Arr_list) in INDIRECT - then the outermost INDEX understands the result of INDEX(ARr_list) refers to a named range.

An example:

=INDEX(INDIRECT(INDEX(ArrayList,2)),2)

enter image description here

Upvotes: 1

Related Questions