SOLVED

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

%3CLINGO-SUB%20id%3D%22lingo-sub-2685054%22%20slang%3D%22en-US%22%3EUse%20text%20in%20a%20cell%20to%20reference%20the%20name%20of%20an%20Array%20for%20INDEX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685054%22%20slang%3D%22en-US%22%3E%3CP%3EHello%2C%3CBR%20%2F%3E%3CBR%20%2F%3EI%20am%20trying%20to%20modify%20a%20manual%20workbook%20used%20in%20my%20company.%3CBR%20%2F%3EI%20am%20using%20INDEX(Array%2CColumn(A%241))%20and%20dragging%20it%20across%20to%20bring%20info%20in%20from%20an%20array.%3CBR%20%2F%3EI%20have%20to%20do%20this%20with%20a%20ton%20of%20arrays%20but%20the%20rows%20where%20the%20data%20ends%20up%20already%20happen%20to%20contain%20the%20name%20of%20my%20arrays.%3CBR%20%2F%3E%3CBR%20%2F%3EFor%20example%20on%20sheet%201%20I%20have%20an%20array%20named%20Apples%20with%20all%20different%20apple%20prices%20in%201%20column.%3C%2FP%3E%3CP%3EOn%20sheet%202%20I%20have%20a%20row%20where%20cell%20A1%20is%20Apples%20then%20I%20need%20to%20fill%20in%20apple%20prices%20across%20from%20B1%20to%20Z1%3C%2FP%3E%3CP%3ESo%20I%20do%20this%20%3DINDEX(Apples%2CColumn(A%241))%20and%20drag%20it%20over.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECan%20I%20replace%20the%20array%20argument%20with%20something%20that%20will%20pull%20the%20name%20%22Apples%22%20and%20use%20it%20as%20the%20name%20of%20the%20array%20in%20the%20formula%20like....%3C%2FP%3E%3CP%3E%3DINDEX(%22Name%20from%20A1%20recognized%20as%20the%20name%20of%20the%20array%22%2C%20Column(A%241))%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EPlease%20let%20me%20know%20if%20my%20request%20is%20clear.%3C%2FP%3E%3CP%3EThanks%20for%20any%20help!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2685054%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685082%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20text%20in%20a%20cell%20to%20reference%20the%20name%20of%20an%20Array%20for%20INDEX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685082%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137621%22%20target%3D%22_blank%22%3E%40ANEX_Brett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EPerhaps%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-excel-formula%22%3E%3CCODE%3E%3DTRANSPOSE(INDIRECT(A1))%3C%2FCODE%3E%3C%2FPRE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685168%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20text%20in%20a%20cell%20to%20reference%20the%20name%20of%20an%20Array%20for%20INDEX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685168%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EDo%20you%20mean%20to%20put%20that%20in%20place%20of%20ARRAY%3F%3C%2FP%3E%3CP%3ELike%20this%3F%3CBR%20%2F%3E%3DINDEX(TRANSPOSE(INDIRECT(A1)%2CCOLUMN(A%241)))%3CBR%20%2F%3E%3CBR%20%2F%3EThis%20didn't%20work%20for%20what%20I%20want.%20I%20need%20the%20content%20of%20cell%20A1%20to%20tell%20the%20index%20function%20what%20the%20name%20of%20the%20array%20is.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685302%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20text%20in%20a%20cell%20to%20reference%20the%20name%20of%20an%20Array%20for%20INDEX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685302%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137621%22%20target%3D%22_blank%22%3E%40ANEX_Brett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo.%20Just%20use%20the%20formula%20as%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3Bhas%20shown%20it.%26nbsp%3B%20It%20is%20an%20array%20formula%20that%20interprets%20the%20contents%20of%20A1%20as%20a%20range%20reference%20and%20transposes%20the%20result.%26nbsp%3B%20You%20may%20need%20CSE%20to%20display%20the%20resulting%20array%20in%20older%20versions%20of%20Excel.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2685306%22%20slang%3D%22en-US%22%3ERe%3A%20Use%20text%20in%20a%20cell%20to%20reference%20the%20name%20of%20an%20Array%20for%20INDEX%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2685306%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1137621%22%20target%3D%22_blank%22%3E%40ANEX_Brett%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI%20mean%20if%20you%20have%20named%20range%20in%20column%20A%20like%20this%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%2097px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305758i87BF635DD876AD0C%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3Ethis%20formula%20returns%20it%20in%20first%20column%20of%20next%20sheet%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20506px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F305760iAE69DEA22D4FA4DB%2Fimage-size%2Flarge%3Fv%3Dv2%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3EIt%20slightly%20depends%20on%20your%20Excel%20version%20how%20to%20use%20this%20formula.%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

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

@ANEX_Brett 

Perhaps

=TRANSPOSE(INDIRECT(A1))

@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.

@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.

@ANEX_Brett 

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

image.png

this formula returns it in first column of next sheet

image.png

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

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.

@ANEX_Brett , you are welcome, glad it helped