Overview
The Excel MAKEARRAY function returns a calculated array with a set number of rows and columns by applying a LAMBDA function. The MAKEARRAY function is useful when creating an array with calculated values.
Syntax
=MAKEARRAY(rows, columns, function)
Arguments
rows – The number of rows in the array.
columns – The number of columns in the array.
function – A LAMBDA that is called to create the array.
Version
Excel 365
Purpose
The Excel MAKEARRAY function returns a calculated array with a set number of rows and columns by applying a LAMBDA function. The MAKEARRAY function is useful when creating an array with calculated values.
The MAKEARRAY function has three required arguments: rows, columns and function. Rows is the number of rows to create in the array. Columns is the number of columns to create in the array. Function is a LAMBDA that is called to perform the calculation used in the array. Like any other array, the total number of values in the array will be a result of the number of rows * the number of columns.
=MAKEARRAY(rows, columns, function)
Examples
Let’s look at an example where we create a multiplication table based on the row and column values:
The formula we have in cell B4 (and spills to adjacent cells) is:
Let’s take a moment to break the syntax of the above formula down into it’s individual pieces:
Looking at the image above, we see values 1, 2, 3 and 4 across row 4. This is as a result of r1 * c1 = 1, r1*c2 = 2, r1*c3 = 3 and r1*c4 = 4. The same can be said for the values in column B. Looking at cell E7, this value is as a result of r4*c4 = 16.
The MAKEARRAY function can also be used to create a defined set of random values. We can see this below:
The formula we have in cell B4 is:
=MAKEARRAY(1, 10, LAMBDA(r, c, RANDBETWEEN(0,100)))
Here we are creating an array with 1 row and 10 columns, or a 1×10 array. We then use the RANDBETWEEN function to generate random numbers between 0 and 100 for each value in the array.
Notes
- An invalid LAMBDA function or an incorrect number of parameters will return a #VALUE! error
- Setting a row or column argument to a value < 1 or to a non-number will return a #VALUE! error
Author
Kyle Stott
Certified Microsoft Excel Expert
Kyle has worked professionally with Microsoft Excel for over a decade and has been consulting on and teaching best practices in Microsoft Excel to over 400 companies across 30+ countries.
Want to learn more?
Join My Excel Academy Today!
This All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
All-Access Pass Includes:
- Access to All My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
30-Day Money-Back Guarantee
Want to learn more?
Join My Excel Academy Today!
30-Day Money-Back Guarantee
This all-access pass includes:
- Access to all My Excel Academy Courses
- 20+ hours of on-demand video
- 1-on-1 instructor support
- Mobile learning supported
- Frequently updated content
- Learn at your own pace
- 500+ practice problems
- Certificate of completion
Don’t hesitate – join now!
