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

[ANEX_Brett]

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!

[#2]

@ANEX_Brett 

Perhaps

=TRANSPOSE(INDIRECT(A1))

[#3]

@Sergei Baklan 

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.

[#4]

@ANEX_Brett 

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.

[#5]

@ANEX_Brett 

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.

[#6]

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!

[#7 · Accepted solution]

@ANEX_Brett 

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]

Wonderful!
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

=INDEX(INDIRECT($A1),COLUMN('SHEET1'!A$1))

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

[#9]

@ANEX_Brett , you are welcome, glad it helped