# How to use the MAKEARRAY function

**What is the MAKEARRAY function?**

The MAKEARRAY function returns an array with a specific number of rows and columns calculated by applying a LAMBDA function formula and the result populates each container in the array.

#### Table of Contents

## 1. Introduction

**What is an array in Excel?**

An array in Excel is a collection of values arranged in rows and columns. It can be thought of as a table or a grid of data.

There are two types of arrays. One-dimensional arrays: A single row or column of data. Two-dimensional arrays: Data organized in both rows and columns.

There are two types of array formulas: The first type returns a single value and the second type returns multiple values. An array formula is a formula that can perform multiple calculations on one or more sets of values.

Excel 365 subscribers have access to dynamic array formulas, a powerful feature that automatically adjusts its output range. These formulas populate the initial target cell and expand into neighboring cells as needed, adapting their size based on the formula's result. This automatic expansion and contraction of the output range is the key characteristic that gives them the name "dynamic" array formulas. The process of extending results into adjacent cells is known as "spilling.

Excel processes arrays in RAM allowing for rapid computations. However, when array sizes exceed available memory, Windows may resort to using virtual memory on the hard drive or SSD. This fallback to disk storage significantly slows down calculations, as accessing data from these devices is much slower than from RAM.

**What is the LAMBDA function?**

MAKEARRAY uses LAMBDA as its third argument to define how each cell in the array should be calculated. This combination allows you to create complex, dynamic arrays based on row and column positions.

The LAMBDA function is required in the MAKEARRAY function, you can't leave it out.

## 2. Syntax

MAKEARRAY(*rows*, *cols*, lambda(*row*, *col*, *calculation*))

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. Example 1

This basic example creates an array with five rows and five columns and 25 containers in total. Each container is populated with value "Yes!".

Formula in cell B2:

This formula in cell B2 is a dynamic Excel 365 formula that spills values to cells below and to the right automatically. This is a feature new to Excel 365.

This simple Excel 365 formula is the only technique that I know of that can create any array and populate all containers with the same value. Perhaps you know a different way? Please comment.

### Explaining the formula

#### Step 1 - Define LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, â€¦,] calculation)

LAMBDA(r,c,"Yes!")

This is the LAMBDA function that defines what will be in each cell of the array. r and c are parameters representing the current row and column, respectively. Value "Yes!" is the output for every cell, regardless of its position.

The LAMBDA function doesn't use the r and c parameters in this case, so the output is the same for every cell.

#### Step 2 - Create array

MAKEARRAY(5,5,LAMBDA(r,c,"Yes!"))

This creates a 5x5 array (5 rows and 5 columns). This is a simple example, but it demonstrates how MAKEARRAY can quickly generate an array of any size and populate it with anything.

## 4. Example 2

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.

The difference lies in the arrangement of values. In cells B9:E14, the data is reorganized from the bottom-right corner to the top-left corner of the original dataset. In contrast, cells B16:G19 display a straightforward transformation where the original rows are converted into columns, and the original columns become rows, maintaining the sequence of data but altering its orientation.

Excel 365 formula in cell B9:

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

Reorganizing data from the bottom-right corner to the top-left corner can be useful in several scenarios:

- If the original data is in chronological order this transformation could quickly give you the most recent data first.
- In task lists or project management, if higher priority items are at the bottom, this reorganization brings them to the top.
- In some sports leagues teams at the bottom of the table are relegated. This transformation could highlight those teams.
- For analyzing trends in reverse such as looking at the most recent quarterly results first.
- If older stock is at the top of the list, this brings newer items to the forefront.

This specific transformation is less common than simple transposition or sorting.

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