MAP function

The MAP function in Excel is a dynamic array function that allows you to apply a given formula or expression to each element in an array (or multiple arrays) and return a new array of the same size with the results. This function is available in Excel 365 and Excel 2021 versions.

Syntax:

=MAP(array1, array2, ..., LAMBDA(array_element, formula))

• array1, array2, …: These are the arrays (or ranges) on which you want to perform the function. You can use multiple arrays, and they should be of the same size.
• LAMBDA(array_element, formula): The LAMBDA function allows you to define a custom operation that will be applied to each element in the arrays.

Example:

Suppose you have two arrays:

• Array1: {1, 2, 3}
• Array2: {4, 5, 6}

If you want to add the corresponding elements of both arrays, you can use the MAP function as follows:

=MAP(A1:A3, B1:B3, LAMBDA(x, y, x + y))

This will return {5, 7, 9} because it adds each pair of values from the arrays.

Benefits:

• Dynamic Array: The function will automatically spill over to adjacent cells, populating them with the results.
• Custom Operations: You can define complex formulas and logic using the LAMBDA function, making MAP very flexible for a wide range of applications.