Use text in a cell to reference the name of an Array for INDEX

New Contributor


I am trying to modify a manual workbook used in my company.
I am using INDEX(Array,Column(A$1)) and dragging it across to bring info in from an array.
I have to do this with a ton of arrays but the rows where the data ends up already happen to contain the name of my arrays.

For example on sheet 1 I have an array named Apples with all different apple prices in 1 column.

On sheet 2 I have a row where cell A1 is Apples then I need to fill in apple prices across from B1 to Z1

So I do this =INDEX(Apples,Column(A$1)) and drag it over.


Can I replace the array argument with something that will pull the name "Apples" and use it as the name of the array in the formula like....

=INDEX("Name from A1 recognized as the name of the array", Column(A$1))


Please let me know if my request is clear.

Thanks for any help!

8 Replies




@Sergei Baklan 


Do you mean to put that in place of ARRAY?

Like this?

This didn't work for what I want. I need the content of cell A1 to tell the index function what the name of the array is.


No. Just use the formula as @Sergei Baklan has shown it.  It is an array formula that interprets the contents of A1 as a range reference and transposes the result.  You may need CSE to display the resulting array in older versions of Excel.


I mean if you have named range in column A like this


this formula returns it in first column of next sheet


It slightly depends on your Excel version how to use this formula.

Ok I understand what you're saying.
I made a test sheet and typed it in just as you have shown and I get a #VALUE!
I'm using Office Pro Plus 2013.

Thank you!
best response confirmed by ANEX_Brett (New Contributor)


On 2013 you shall use it as array function, i.e. using Ctrl+Shift+Enter to enter the formula. It is explained in details here TRANSPOSE function - Office Support (


If you prefer INDEX and drag cells to the right, it'll be like


This is what I got to do exactly what I want. I can drag down for the next array and drag across to fill in the values.


On sheet 2 I used this forumla



Thank you for the quick help. I appreciate it. I don't think I would have quite figured this out on my own.

@ANEX_Brett , you are welcome, glad it helped