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.

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.

Back to top

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

Back to top

'MAKEARRAY' function examples

'MAKEARRAY' function examples

'MAKEARRAY' function examples

'MAKEARRAY' function examples

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

Comments (3)