How to use the MAP function
What is the MAP function?
The MAP function is a new Excel 365 function that requires the LAMBDA function to work. It passes all values in an array to a LAMBDA function, which then calculates new values based on a formula you specify. It finally returns an array with the same size as the original array.
In other words, the process involves applying a specified formula to every element in an array or range. When multiple arrays are used, values are paired up based on their positions across the arrays. This pairing allows the formula to be applied to corresponding elements from each array. The following examples will illustrate this concept thoroughly.
Table of Contents
1. Introduction
What is an array in Excel?
An array in Excel is a collection of values arranged in rows and columns. It can be thought of as a table or a grid of data.
There are two types of arrays. One-dimensional arrays: A single row or column of data. Two-dimensional arrays: Data organized in both rows and columns.
There are two types of array formulas: The first type returns a single value and the second type returns multiple values. An array formula is a formula that can perform multiple calculations on one or more sets of values.
Excel 365 subscribers have access to dynamic array formulas, a powerful feature that automatically adjusts its output range. These formulas populate the initial target cell and expand into neighboring cells as needed, adapting their size based on the formula's result. This automatic expansion and contraction of the output range is the key characteristic that gives them the name "dynamic" array formulas. The process of extending results into adjacent cells is known as "spilling.
Excel processes arrays in RAM allowing for rapid computations. However, when array sizes exceed available memory, Windows may resort to using virtual memory on the hard drive or SSD. This fallback to disk storage significantly slows down calculations, as accessing data from these devices is much slower than from RAM.
What is the LAMBDA function?
The MAP function uses the LAMBDA function as its second argument to define how each cell in the array should be calculated. This combination allows you to perform cumulative calculations across a range of values.
The LAMBDA function is required in the MAP function, you can't leave it out. Read more: LAMBDA function
2. Syntax
MAP(array_1, lambda_or_array<#>)
| array_1 | Required. A cell reference to a cell range or an array. |
| array_n | Optional. A cell reference to a cell range or an array. |
| lambda_or_array | Required. A LAMBDA function, it must be the last argument in the MAP function. Each array uses a different parameter in the LAMBDA function. |
3. Example 1
This example shows how to divide each number in the array B3:C11 by 2. The source data is in cell range B3:C11 and the result is displayed in cell range E3:F11.
Formula in cell E3:
This formula is an Excel 365 dynamic array formula meaning it spills values to adjacent cells as far as needed. This is a simple demonstration of the MAP function, I know that the formula can be made a lot smaller using only the division operator.
Explaining the formula
Step 1 - Build LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
The LAMBDA function is iterated as many times as there are values in cell range B3:C11.
LAMBDA(x,x/2)
The first argument specifies the parameter, the second argument lets you build a formula.
Step 2 - Pass values to the LAMBDA function
The MAP function lets you pass arrays to the LAMBDA function.
MAP(B3:C11, LAMBDA(x,x/2))
The first number is 771 in cell range B3:C11. 771/2 equals 385.5
It then continues with the remaining values returning an array with a size that matches the original array (B3:C11).
4. MAP Function Example 2
This example demonstrates how to pass two arrays to the LAMBDA function. The LAMBDA function adds the first value in the first array B3:B11 to the first value in the second array. In other words, the calculations are based on the value's position in the array.
Formula in cell E3:
The first value in the first array named x (B3) is 771, the first value in the second array y (C3) is 637. The LAMBDA function performs addition based on these values: 771 + 637 equals 1408.
The formula then continues to the next value pairs and continues to add the numbers. For example, the second value is 538 in the first array (B3:B11) and the second value in the second array (C3:C11) is 885. The formula returns 1423 in cell E4 based on 538 + 885 = 1423
Explaining the formula
Step 1 - Build the LAMBDA function
The LAMBDA function build custom functions without VBA, macros or javascript.
Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)
LAMBDA(x,y,x+y)
The two first parameters specifies which parameters to use, they correspond to the arrays in the MAP function.
x+y is the formula in the LAMBDA function.
Step 2 - Pass values to the LAMBDA function
MAP(B3:B11,C3:C11,LAMBDA(x,y,x+y))
x - B3:B11
y - C3:C11
x + y = B3:B11 + C3:C11
5. MAP Function Example 3
A data table containing three columns and 9 rows contains information about a survey. How many numbers in each row exceed 500?
The source data is located in cell range B3:D11, it contains random integers between 1 and 1000. The formula in cell F3 counts the number of integers that meet the condition.
Formula in cell F3:
This formula is an Excel 365 dynamic array formula meaning it spills values to adjacent cells as far as needed. The first values in each cell range B3:B11, C3:C11, and D3:D11 are 771, 637, and 948. They all meet the given condition. This results in 3 in cell F3 meaning there are three numbers larger than 500 in cells B3, C3, and D3.
This is not the case in cells B4, C4, and D4. Only two are larger than 500. They are 538 and 885. 445 is smaller than 500. The formula continues to process each pairs until all values are evaluated.
Explaining formula
Step 1 - Specify arguments in the MAP function
MAP(B3:B11,C3:C11,D3:D11,LAMBDA(...)))
Step 2 - Specify arguments in the LAMBDA function
There are three cell references in the MAP function which must correspond to 3 variables in the LAMBDA function. I named them x, y, and z.
LAMBDA(x,y,z,...)
Step 3 - Define the condition
The larger than character > allows you to create a condition that evaluates to TRUE if numbers are larger than 500. We need to specify all three variables.
x>500,y>500,z>500
Step 4 - Count numbers larger than 500
The SUM function allows you to add numerical values, the function returns the sum in the cell it is entered in. The SUM function is cleverly designed to ignore text and boolean values, adding only numbers.
Function syntax: SUM(number1, [number2], ...)
SUM(x>500,y>500,z>500)
'MAP' function examples
The following article has a formula that contains the MAP function.
This article describes two ways to perform lookups in multiple in multiple cross reference tables simultaneously. The first one being […]
Functions in 'Logical' category
The MAP function function is one of 16 functions in the 'Logical' category.
How to comment
How to add a formula to your comment
<code>Insert your formula here.</code>
Convert less than and larger than signs
Use html character entities instead of less than and larger than signs.
< becomes < and > becomes >
How to add VBA code to your comment
[vb 1="vbnet" language=","]
Put your VBA code here.
[/vb]
How to add a picture to your comment:
Upload picture to postimage.org or imgur
Paste image link to your comment.
Contact Oscar
You can contact me through this contact form