# 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:

This formula spills values to cells below and to the right as far as needed.

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

### 'MAKEARRAY' function examples

This post explains how to lookup a value and return multiple values. No array formula required.

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

Question: I am trying to create an excel spreadsheet that has a date range. Example: Cell A1 1/4/2009-1/10/2009 Cell B1 […]

### 'MAKEARRAY' function examples

This post explains how to lookup a value and return multiple values. No array formula required.

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

### 'MAKEARRAY' function examples

This article explains different techniques that filter rows/records that contain a given text string in any of the cell values […]

This article demonstrates two ways to calculate the number of times each word appears in a given range of cells. […]

VLOOKUP a multi-column range and return multiple values.

### 'MAKEARRAY' function examples

This post explains how to lookup a value and return multiple values. No array formula required.

This article demonstrates how to extract records/rows based on two conditions applied to two different columns, you can easily extend […]

First, let me explain the difference between unique values and unique distinct values, it is important you know the difference […]

### Functions in 'Lookup and reference' category

The COLUMNS function function is one of 25 functions in the 'Lookup and reference' category.

### Excel function categories

### 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!