Forum Discussion
Use text in a cell to reference the name of an Array for INDEX
Hello,
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!
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 (microsoft.com)
If you prefer INDEX and drag cells to the right, it'll be like
=INDEX( INDIRECT(A1), COLUMN(A$1) )
8 Replies
Do you mean to put that in place of ARRAY?
Like this?
=INDEX(TRANSPOSE(INDIRECT(A1),COLUMN(A$1)))
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.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.
