Forum Discussion

Copper Contributor

Aug 25, 2021

Solved

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

excel

Formulas and Functions

SergeiBaklan
Aug 25, 2021
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) )

ANEX_Brett

Copper Contributor

Aug 25, 2021

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!

SergeiBaklan

Diamond Contributor

Aug 25, 2021

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

ANEX_Brett
Copper Contributor
Aug 26, 2021
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.
- SergeiBaklan
  Diamond Contributor
  Aug 26, 2021
  ANEX_Brett , you are welcome, glad it helped