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

### Functions in this article

More than 1300 Excel formulas

## Excel categories

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