How to use the MAKEARRAY function
The MAKEARRAY function returns an array with a specific number of rows and columns calculated by applying a LAMBDA function.
Table of Contents
1. MAKEARRAY Function Syntax
MAKEARRAY(rows, cols, lambda(row,col, calculation))
2. MAKEARRAY Function Arguments
| Argument | Description |
| rows | The number of rows in the array to be created. Must be larger than 0 (zero). |
| cols | The number of columns in the array to be created. Must be larger than 0 (zero). |
| row | Required. A number representing the row in the array, the number changes from cell to cell. |
| col | Required. A number representing the column in the array, the number changes from cell to cell. |
3. Flip/reverse a cell range both horizontally and vertically
This example demonstrates how to flip or reverse values both horizontally and vertically using the MAKEARRAY function, see the blue cell range (B9:E14). The original source data is in a green cell range (B2:E7).
The third cell range colored yellow has values rearranged by the TRANSPOSE function in order to show the difference between transposing values and flip/reverse values.
Excel 365 formula in cell B9:
Explaining formula
Step 1 - Count rows in the given cell range
The ROWS function calculate the number of rows in a cell range.
Function syntax: ROWS(array)
ROWS(B2:E7)-r+1
r is a variable, it starts from one and increments up to the number of rows in B2:E7. This makes the formula start from the bottom and not from the top.
Step 2 - Calculate rows in the given cell range
The COLUMNS function calculates the number of columns in a cell range.
Function syntax: COLUMNS(array)
COLUMNS(B2:E7)-c+1
Step 3 - Get value
The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.
Function syntax: INDEX(array, [row_num], [column_num])
INDEX(B2:E7,ROWS(B2:E7)-r+1,COLUMNS(B2:E7)-c+1)
Step 4 - Build the LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(r,c,INDEX(B2:E7,ROWS(B2:E7)-r+1,COLUMNS(B2:E7)-c+1))
Step 5 - Create and populate array
MAKEARRAY(ROWS(B2:E7),COLUMNS(B2:E7),LAMBDA(r,c,INDEX(B2:E7,ROWS(B2:E7)-r+1,COLUMNS(B2:E7)-c+1)))
More than 1300 Excel formulasExcel categories
3 Responses to “How to use the MAKEARRAY function”
Leave a Reply
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
An alternative to obtain the same result without MAKEARRAY:
= LET( r,ROWS(M), c,COLUMNS(M), INDEX( M, SEQUENCE(r,1,r,-1), SEQUENCE(1,c,c,-1) ) )
where M is the name of the range of cell containing the matrix.
An alternative to obtain the same result is to pre and postmutiply the original matrix by exchange matrices (which have ones in the antidiagonal and zeros elsewhere). Those exchange matrices can be constructed using MAKEARRAY.
Specifically, the original matrix M has to be premultiplied by
=LAMBDA(n,MAKEARRAY(n,n,LAMBDA(r,c,IF(r=n+1-c,1,0))))(ROWS(M))
and postmultiplied by
=LAMBDA(n,MAKEARRAY(n,n,LAMBDA(r,c,IF(r=n+1-c,1,0))))(COLUMNS(M)).
Exchange matrix: https://en.wikipedia.org/wiki/Exchange_matrix
Rodolfo,
Thank you for your comments!