The `MAKEARRAY` function in Excel is used to generate an array by applying a formula or logic that you specify, across a defined number of rows and columns. This function is useful for creating dynamic arrays with custom computations without needing to fill in each cell manually. It was introduced with the Excel version that supports dynamic arrays, like Excel 365 or Excel 2019 onwards.
Syntax
MAKEARRAY(rows, columns, lambda)
- rows: The number of rows you want the resulting array to have.
- columns: The number of columns you want the resulting array to have.
- lambda: A LAMBDA function (or expression) that is applied across the array. It should take two arguments. These arguments represent the row and column index of each cell in the array.
Example
Suppose you want to create a 3×3 array where each element is the sum of its row and column index.
=MAKEARRAY(3, 3, LAMBDA(row, col, row + col))
Explanation
- The function will produce a 3×3 array.
- The `LAMBDA` part of the function defines a mini-function that calculates each element in the array. In this example, it takes the current row and column index and returns their sum.
- For instance, the top-left cell (first row, first column) would be calculated as `1 + 1 = 2`, the second cell in the top row will be `1 + 2 = 3`, and so on.
Steps to Use
- Select a Cell: Click on the cell where you want the top-left corner of the resulting array to be placed.
- Enter the Function: Type the `MAKEARRAY` function directly into the cell, using the desired parameters.
- Press Enter: Once you’ve typed the function, press Enter. Excel will populate the array into the worksheet cells.
Remember
- Dynamic Arrays: The cells covered by the resulting array will change automatically when any dependent data or the defined array size changes. Ensure that there is enough space in your worksheet for the array to expand.
- Supported Versions: The `MAKEARRAY` function only works in Excel 365 and Excel 2019. Ensure your Excel version supports dynamic array functions. If it doesn’t, you’ll need to use different methods to accomplish similar tasks.
By mastering `MAKEARRAY`, you can simplify complex array calculations and make your spreadsheets more dynamic and easier to manage.
Unlock Your Potential
Excel
Access
Power BI
