How to Use MAP and LAMBDA in Google Sheets
LAMBDA(name, formula) defines a small reusable formula. MAP(array, LAMBDA(...)) applies it to every value in a range and returns a new value for each.
What MAP and LAMBDA do
Most functions work on a whole range at once. Sometimes you need your own calculation applied one value at a time, such as adding tax to every price. LAMBDA lets you define that calculation, and MAP runs it over each value in a range, returning one result per value. It is a modern, readable alternative to wrapping things in ARRAYFORMULA.
Syntax
=LAMBDA(name, formula_expression)
=MAP(array1, [array2, ...], LAMBDA)
Per Google's help pages, LAMBDA takes a name, an identifier that stands in for the value you pass in, and a formula that uses it. The name must be an identifier: it cannot be a cell reference such as A1, cannot contain spaces or special characters (dots and underscores are allowed), and cannot start with a number.
MAP applies a LAMBDA to each value in one or more arrays or ranges. The LAMBDA needs exactly one name per input array, otherwise MAP returns #N/A. Each mapped value must be a single value, because array results are not supported. When you use several arrays, they must have the same size.
A step-by-step example
Here are three prices in cells A1:A4.
| A | |
|---|---|
| 1 | Price |
| 2 | 1 |
| 3 | 2 |
| 4 | 3 |
=MAP(A2:A4, LAMBDA(x, x * 2))
This returns 2, 4, 6 down a column. Here is why: MAP takes each value of A2:A4 in turn, passes it into the LAMBDA as x, and the LAMBDA doubles it. Google's own example does the same with LAMBDA(cell, cell*2) over a two-dimensional range.
LAMBDA on its own
A LAMBDA does nothing until it is called with a value. The help page's sample calls it straight away by adding the value in a second set of parentheses:
=LAMBDA(salary, salary * 0.3)(5000)
This returns 1500, 30% of 5000. Without the final (5000) the formula would show an error, because the LAMBDA was never called.
Common mistakes
- An invalid name. A name like
C1clashes with a cell reference. - The wrong number of names. One per array, or MAP returns #N/A.
- A last argument that is not a LAMBDA. The final argument of MAP must be one.
- Returning an array from the LAMBDA instead of a single value.
Try it yourself
Use the prices above (A2:A4). Type your formula into the box, starting with =, then press Check.
Exercise 1: Double every value
Write a formula that doubles every value in A2:A4 using MAP and a LAMBDA.
Exercise 2: Call a LAMBDA
Write a formula that defines a LAMBDA multiplying a value by 0.3 and calls it with 5000.
What to learn next
- LET: name a calculation and reuse it.
- ARRAYFORMULA: run one formula down a whole column.
- SEQUENCE: generate a list of numbers.
Syntax and rules in this guide were checked against Google's MAP/LAMBDA help page. See all guides.