LAMBDA defines a calculation using named inputs. Call it directly by adding values in parentheses, or pass it to a helper such as MAP, BYROW or REDUCE.
LAMBDA function syntax
=LAMBDA(name1, [name2, ...], formula_expression)- Names: valid identifiers, not cell references or names containing spaces.
- formula_expression: calculation using the names.
- A direct call supplies a value for each name after the definition.
Set up the example data
Enter this small dataset starting in A1. The first row contains headers. Keep the formula output separate from the input cells.
| Q1 | Q2 |
|---|---|
| 10 | 20 |
| 15 | 25 |
| 20 | 30 |
Call a LAMBDA directly
Enter this formula in A10. The first parentheses define x squared. The second supplies five. A helper function is not required for this direct call.
=LAMBDA(x,x^2)(5)Result: 25.
Square a list with MAP
MAP supplies each source value automatically. The LAMBDA needs one name for this one-array example.
=MAP(A2:A4,LAMBDA(x,x*x))Result: 100; 225; 400.
Total each row with BYROW
BYROW passes an entire row to r. SUM turns that row into the single value required for each result.
=BYROW(A2:B4,LAMBDA(r,SUM(r)))Result: 30; 40; 50.
Average each column with BYCOL
BYCOL passes one complete column at a time. Two columns produce two averages across one result row.
=BYCOL(A2:B4,LAMBDA(c,AVERAGE(c)))Result: 15, 25.
Reduce a range to one total
REDUCE starts at zero and adds each value to its accumulator. It returns the final total, not the intermediate running totals. SUM is simpler for plain addition.
=REDUCE(0,A2:A4,LAMBDA(acc,v,acc+v))Result: 45.
Display running totals with SCAN
SCAN returns each intermediate accumulator value. This is the helper to choose when every running total needs to be visible.
=SCAN(0,A2:A4,LAMBDA(acc,v,acc+v))Result: 10; 25; 45.
Generate a multiplication table
MAKEARRAY supplies a row index and column index, both starting at one. The expression multiplies them to fill a new grid.
=MAKEARRAY(3,3,LAMBDA(r,c,r*c))Result: 1, 2, 3; 2, 4, 6; 3, 6, 9.
Other Google Sheets articles you may also like