Excel MAKEARRAY function

Summary

The Excel MAKEARRAY function generates a calculated array of a specified size by applying a LAMBDA function to each element based on its row and column indices. This function is versatile for creating dynamic arrays, data simulations, or any structured data pattern, enhancing Excel’s capability for array manipulation and custom data generation.
Syntax
				
					=MAKEARRAY(rows, cols, lambda(row, col))
				
			
  • rows: The number of rows in the array. Must be greater than zero.
  • cols: The number of columns in the array. Must be greater than zero.
  • lambda: A LAMBDA function that is called for each element in the array. It takes two parameters:
    • row: The row index.
    • col: The column index.
Return value
A calculated array of the specified size.

How to use

Define the desired size of your array with rows and cols, then specify a LAMBDA function that calculates the value for each element based on its row and col position. The LAMBDA function allows for flexible and complex data generation within the array.

Examples

Simple MAKEARRAY
Creating a Multiplication Table: Generating a simple 3×3 multiplication table.
				
					=MAKEARRAY(3, 3, LAMBDA(r, c, r * c))

				
			
This formula creates a 3×3 array where each element is the product of its row and column indices, demonstrating a basic use of MAKEARRAY for educational or analytical purposes.
MAKEARRAY for Random Colors
Generating a Random Color List: Creating an array filled with random color names.
				
					=MAKEARRAY(2, 3, LAMBDA(row, col, CHOOSE(RANDBETWEEN(1, 3), "Red", "Blue", "Green")))

				
			
This example produces a 2×3 array with each cell randomly assigned one of three colors, showcasing MAKEARRAY’s utility in simulations or randomized data sets.
MAKEARRAY with Zero Values
Initializing an Array with Zeros: Filling an array with zeros.
				
					=MAKEARRAY(2, 3, LAMBDA(r, c, 0))

				
			
Generates a 2×3 array where every element is 0, useful for initializing data structures in financial models or other applications.
MAKEARRAY for Custom Labels
Creating an Array of Custom Labels: Populating an array with a repeating label.
				
					=MAKEARRAY(2, 3, LAMBDA(r, c, "x"))

				
			
This creates a 2×3 array where every cell contains the label “x”, demonstrating how MAKEARRAY can be used for template generation or visual data organization.
MAKEARRAY for Random Letters
Generating Random Uppercase Letters: Filling an array with random uppercase letters A-Z.
				
					=MAKEARRAY(2, 3, LAMBDA(r, c, CHAR(RANDBETWEEN(65, 90))))

				
			
Produces a 2×3 array of random uppercase letters, illustrating MAKEARRAY’s application in data anonymization, testing, or educational tools.

Additional Notes

  • MAKEARRAY, combined with the flexibility of LAMBDA functions, significantly expands Excel’s capabilities for dynamic array creation and manipulation.
  • This function is particularly useful for advanced users looking to generate complex data patterns, perform simulations, or create custom data structures without requiring external programming or complex formulas.

Related Functions

Excel BYCOL function

The Excel BYCOL function applies a lambda function to each column in a range or array, streamlining column-based calculations.

Excel LET function

The Excel LET function assigns names to calculation results, simplifying formulas and improving performance by reducing repetition.

Excel LAMBDA function

The Excel LAMBDA function creates custom functions without VBA, enabling complex calculations and reusable formula components.

Excel REDUCE function

The Excel REDUCE function aggregates elements of an array, allowing for cumulative calculations and data reduction operations.

Excel SCAN function

The Excel SCAN function cumulatively applies a lambda function across an array, generating an array of intermediate results.

Excel MAP function

The Excel MAP function applies a lambda function to each element in an array or range, enhancing data transformation capabilities.

Content Navigation