How to use the MAP function
The MAP function passes all values in an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then returns an array with the same size as the original array.
Table of Contents
1. MAP Function Syntax
MAP(array1, lambda_or_array<#>)
2. MAP Function Arguments
| array1 | Required. A cell reference to a cell range or an array. |
| lambda_or_array | Optional. Additional arrays. 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. MAP Function Example 1
This example shows how to divide each number in the array B3:C11 by 2.
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.
Formula in cell D3:
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 to the second value on the same row.
The first value in the array x is 771, the second value in the array y is 637. 771 + 637 equals 1408.
Formula in cell E3:
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
'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 many 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