The Excel MAKEARRAY function lets you generate a calculated array of a specified size using a LAMBDA function. Users have full control over the values in the array. They decide how each one of them should be calculated.
For example, suppose that you are creating a discount matrix for your online store. Refer to the following image.
You have categorised all products into A, B, and C classes. You plan to offer a discount based on:
- Number of units sold (between 1 and 5)
- Category Level (A, B, and C)
Additionally, a flat 2% discount applies to all products, no matter the quantity or category.
Discount = Units Sold x Category Level x 2%
So, your Excel formula to generate this discount matrix will be as follows:
=MAKEARRAY(5, 3, LAMBDA(r, c, r * c * 0.02))Where,
- 5 is the number of rows, which depends on the units sold
- 3 is the number of columns, which depends on the category level
- LAMBDA(r, c, r * c * 0.02) is the formula to calculate the discount based on the row and column numbers (it also includes a 2% flat discount)
- r is the first parameter for the LAMBDA function
- c is the second parameter for the LAMBDA function
The outputs are displayed in the following GIF,
Syntax
The MAKEARRAY is a powerful logical function. It is combined with the other functions in Excel to create complex formulas.
=MAKEARRAY(rows, columns, function)Where,
- rows need to be replaced with the desired number of rows you wish to create
- columns need to be replaced with the desired number of columns you wish to create
- function must be a LAMBDA formula
Important Notes:
- The function won’t work as expected if you fail to specify the number of rows and columns properly.
- Each value in the calculated array is based on its corresponding row and column position.
- You may get a #CALC! error if the LAMBDA function is not properly defined.
- The output is dynamic.
- It can create up to 16,384 rows and columns.
- Make sure to keep the target cell range empty, as all results are spilled into the worksheet starting from the formula error.
How to use the MAKEARRAY Function in Excel
Download the example spreadsheet used to demonstrate the MAKEARRAY function in the upcoming section. Practice is the key to success!
Example 1: MAKEARRAY Function Basics
Let us start with a basic example. Suppose that your task is to create a multiplication chart within Excel.
We have a 10×10 grid as shown in the above image. It means we need a list of multiples from 1 to 10 of a number.
Here are the steps,
- Select the cell C5
- Type =MAKEARRAY
- Select the first option from the popup
- Type 10 in the place of the rows argument
- Type , to move to the next argument
- Then, type 10 in the place of the columns argument
- Put ,
- Specify LAMBDA
(As the LAMBDA is a built-in function in Excel, you will see the following popup) - Double-click the LAMBDA command from the list
- Specify rows as the first parameter for the LAMBDA function
- Type ,
- Specify columns as the second parameter for the LAMBDA function
- Type ,
- Put rows * columns as the calculation
- Complete the bracket for the LAMBDA function using )
- Complete the bracket for the MAKEARRAY function using )
- Hit the Enter key
Our final formula is as follows,
=MAKEARRAY(10, 10, LAMBDA(rows, columns, rows * columns))Where,
- First 10 is the required number of rows
- Second 10 is the required number of columns
- LAMBDA(rows, columns, rows*columns) is the LAMBDA formula to calculate each cell of the multiplication chart
- rows is the first parameter for the LAMBDA function
- columns is the second parameter for the LAMBDA function
- rows * columns is the calculation for the LAMBDA function
The results are displayed in the following image,
To learn more about the LAMBDA function, please read our article: Excel LAMBDA Function – An Ultimate Tool to Create Custom Functions in Excel.
Example 2: Combining MAKEARRAY with Other Functions in Excel
Suppose that you manage an e-commerce store that has 10 different products across 5 regional warehouses. Your task is to create an Inventory Reorder Matrix based on available stocks across these warehouses.
Let’s assume that those 10 products are:
- Apple
- Banana
- Orange
- Mango
- Grapes
- Pineapple
- Papaya
- Watermelon
- Kiwi
- Pomegranate
You want the matrix as follows,
Let us assume that each product’s stock depends upon its corresponding position in the given array, multiplied by 5.
For example, the stock of the Mangoes in Warehouse 3 will be: 4 x 3 x 5 = 60.
Where 4 is the row number and 3 is the column number. Refer to the following image,
Now, let us create the Inventory Reorder Matrix by combining the MAKEARRAY, LAMBDA, and IF functions in Excel.
Our final formula will be as follows,
=MAKEARRAY(10, 5, LAMBDA(product, warehouse, IF((product * warehouse * 5) < 50, "Reorder", "OK")))Where,
- 10 is the required number of rows, which depends on the total products
- 5 is the required number of columns, which depends on the number of regional warehouses
- LAMBDA(product, warehouse, IF((product * warehouse * 5) < 50, “Reorder”, “OK”)) is the LAMBDA formula to display the desired message if the stock is below 50
- product is the first parameter for the LAMBDA function
- warehouse is the second parameter for the LAMBDA function
- product * warehouse * 5 is the formula to calculate the stock
- (product * warehouse * 5) < 50 is the expression to check if the stock is less than 50 quantities
- Reorder is the desired output if the stock is below 50
- OK is the desired output if the stock is greater than 50
The results are displayed in the following GIF,
Takeaway
The MAKEARRAY function is useful when you wish to create an array with a specific number of rows and columns. Make sure to define the LAMBDA formula properly, as it decides what values should populate the array.
I hope this article taught you all the bells and whistles of the MAKEARRAY function. Please comment below if you are stuck or encounter any particular error while using the MAKEARRAY function. I will answer your questions as soon as possible.
Additional Resources:
- IF Function in Excel
- LAMBDA Function in Excel
- MAP Function in Excel
- SCAN Function in Excel
- IFERROR Function in Excel
- Conditional Formatting in Excel
Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.