## 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 formulas### Excel 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.

**Contact Oscar**

You can contact me through this contact form

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!