Mastering Dynamic Arrays #2: Selecting Array Rows/Columns
This is the second of what I hope will be a fairly frequent series on mastering the magic of Dynamic Arrays (DAs). The previous post introduced SEQUENCE() which generates 1 and 2 dimensional arrays. This post is about ways to select specific rows or columns from arrays.
NOTE: If this is not new to you, please post for the readers how you do this in the comments. Thanks.
To start, we will create a 2 dimensional array by entering this formula in cell A1.
=SEQUENCE( 5, 7)
A1 is the upper left corner of this array and the only cell with a formula. A1# (note the hashtag, aka pound sign) is the address of the entire array.
Get one row or column using INDEX( array, row number, column number)
To get the array's second row we can use INDEX( ) like so:
=INDEX( A1#, 2, )
A1# is the entire array. 2 is the row we want. By leaving the column argument empty, index retrieves all columns. NOTE! We must include the comma after the row number.
We can retrieve the third column similarly:
=INDEX( A1#, , 3)
Get multiple rows of columns using CHOOSECOLS( array, col_num1, [col_num2], …) and CHOOSEROWS()
INDEX() works well for one row or one column. If we want multiple rows or columns we should use CHOOSEROWS() and CHOOSECOLS(). To get the second and fourth rows we can use this formula:
=CHOOSEROWS( A1#, 2, 4)
To get the second and fourth columns we can use this formula:
=CHOOSECOLS( A1#, 2, 4)
Of course, CHOOSEROWS() and CHOOSECOLS() can retrieve just one row or column so the INDEX() option is a bit redundant so I will be standardizing on CHOOSEROWS() and CHOOSECOLS() in my work.
In the next post we will discuss two other functions for retrieving array sections: TAKE() and DROP().
Assistant Professor - University of Coimbra
9 个月great! but how can you select the columns to display dynamically, based on a string placed in a single cell? Any ideas? thanks!
Senior Business Intelligence Consultant
1 年I agree on CHOOSECOLS over INDEX but a downside is when I want to eg SUM the column. Both are very absolute references with the same result even if copied and pasted, aiming to get the result of each column. ? Using the old but seldom seen intersection character, space, the formula will be possible to fill right; =SUM($A$1# A:A) where in this case the core reference is =$A$1# A:A The sum formula can be used in A9 and filled right to G9. ? Kind of goal may be dynamic adjustment also sideways like in A11 =BYCOL(A1#,LAMBDA(range,SUM(range))) …but there are quite a few steps between space and BYCOL.
Valuation, Modeling, Analytics || 55K+ Followers || MS Excel (Spreadsheet) Expert || Project Finance || Trainer & Cool Mentor || De-centralization
1 年Insightful as always! Standardizing on choosecols is great idea, they are easier to read/intuitive. Also, Craig any thoughts on array of arrays limitation in Excel? This is my formula on Google Sheets, but it's not working on Excel. Do you think Microsoft shall introduce this or its not required?
Microsoft EXCEL and VBA Expert. Lethal lefty on the Tennis Court! Reads books about Enki and the Anunnaki.
1 年Nice article! Working with arrays can be a game changer … if … you know how it works! ??