SOLVED

New Contributor

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

8 Replies

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

Perhaps

``=TRANSPOSE(INDIRECT(A1))``

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

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.

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

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.

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

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.

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

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)
Solution

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

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) )``

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

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.

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

@ANEX_Brett , you are welcome, glad it helped