Excel MAP function

Summary

The Excel MAP function transforms each value in one or more arrays into a new value by applying a specified LAMBDA function. This powerful feature enables complex array manipulations and custom calculations on array elements, offering a high degree of flexibility in data processing and analysis within Excel.
Syntax
				
					=MAP(array1, [array2, …,] lambda_or_array<#>)
				
			
  • array1, [array2, …,]: Arrays to be mapped. MAP can process multiple arrays simultaneously, provided they have the same dimensions.
  • lambda_or_array<#>: A LAMBDA function that takes parameters matching the number of provided arrays and performs calculations on their elements. Alternatively, another array to be mapped can be provided.
Return value
An array where each element is the result of applying the LAMBDA function to corresponding elements from the input arrays.

How to use

Define the array(s) you want to transform and specify a LAMBDA function that describes how each element should be processed. The LAMBDA function must accept as many parameters as there are arrays, and it must return a result based on those parameters.

Examples

Simple MAP
Squaring Numbers Conditionally: Applying a condition to square numbers above a certain value.
				
					=MAP(A1:C2, LAMBDA(a, IF(a > 4, a * a, a)))

				
			
This formula squares each number greater than 4 in the array A1:C2, leaving numbers 4 or less unchanged, showcasing MAP’s ability to conditionally transform array elements.
MAP for Logical Operations
Identifying Rows Where Both Conditions are True: Finding rows where two criteria in corresponding columns are met.
				
					=MAP(TableA[Col1], TableA[Col2], LAMBDA(a, b, AND(a, b)))

				
			
This example checks two columns for logical TRUE and returns an array indicating where both conditions are satisfied, illustrating MAP’s utility in complex logical testing.
MAP with Multiple Criteria
Filtering Based on Size and Color: Using MAP to filter a dataset for specific attributes.
				
					=FILTER(D2:E11, MAP(D2:D11, E2:E11, LAMBDA(s, c, AND(s = "Large", c = "Red"))))

				
			
Filters for rows where the “Size” is “Large” and the “Color” is “Red”, demonstrating how MAP can facilitate multi-criteria data filtering.
MAP for Arithmetic Transformations
Incrementing Each Array Element: Adding 1 to every item in an array.
				
					=MAP({1, 2, 3}, LAMBDA(a, a + 1))

				
			
Increments each element of the array by 1, illustrating a basic arithmetic transformation using MAP.
MAP to Clean Data
Removing Non-numeric Values: Employing MAP to filter out non-numeric values from a dataset.
				
					=MAP(B5:D16, LAMBDA(a, IF(ISNUMBER(a), a, "")))

				
			
Transforms a 12×3 array, replacing non-numeric values with blank strings, thereby cleaning the data for further analysis or visualization.

Additional Notes

  • MAP is particularly useful when you need to apply custom functions or conditions to array data, going beyond what’s possible with Excel’s standard array operations.
  • It excels in scenarios requiring element-wise transformations, logical tests, or when working with data that requires conditional processing before analysis.

Related Functions

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 LET function

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

Excel BYROW function

The Excel BYROW function applies a lambda function to each row in a range or array, facilitating row-based operations and analysis.

Excel BYCOL function

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

Excel LAMBDA function

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

Content Navigation