The MAP function is useful when you want to process each item in an array individually but as an array operation that yields an array result. MAP is also useful when the formula logic is complex and would be best managed in a single location. Using a named LAMBDA function with MAP is possible to reuse the same code elsewhere. The MAP function takes two required arguments: array1 and lambda. Array1 is the array that should be mapped. Lambda is the custom lambda function that should be run on each item in array1. Additional arguments can be added in the form of array2, array3, etc. before lambda, which must always be the last argument provided. When additional arrays are provided to MAP, the LAMBDA function must be configured to accept additional arguments. Array1 should be the first argument in the LAMBDA, array2 should be the second argument, etc.
Examples
The MAP function maps each value in an array to a custom LAMBDA function. For example, the formula below maps a LAMBDA function that simply adds 1 to each item in the supplied array: The variable a passed into the LAMBDA function as the first argument is array1 in the MAP function.
Remove non-numeric values
In the worksheet shown above, MAP is used to remove non-numeric values from the array provided (B5:D16). The formula in F5 is: Because array1 has 12 rows and 3 columns, the result from MAP is a 12 x 3 array that spills into the range F5:H16. Note: the +a notation is used to force Excel to coerce a from a range reference to an array of values. Without the +, MAP will return a #CALC! error. This step is sometimes necessary when working with array formulas that spill.
Additional arrays
MAP can accept additional arrays that can be used by the LAMBDA during calculation. The formula below adds each item in array1 to the corresponding item in array2: Here, the variable a given to the LAMBDA function is array1 in the MAP function, and b is array2.
When to use MAP
The dynamic array engine in Excel can handle many array operations natively without a function like MAP. For example, the formulas below both return the same result: As such, there is no particular reason to use MAP when a native array operation will achieve the same result. However, there are cases where MAP can be useful: Note: The MAP function returns an array of results. See the REDUCE function if you want to process each item in an array individually but you want a single aggregated result.
Dave Bruns
Hi - I’m Dave Bruns, and I run Exceljet with my wife, Lisa. Our goal is to help you work faster in Excel. We create short videos, and clear examples of formulas, functions, pivot tables, conditional formatting, and charts.
