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.