Traditional spreadsheet calculations often require copying the same formula down hundreds of rows, or relying on clumsy ARRAYFORMULA hacks that break when combined with logical functions like AND and OR. Google Sheets revolutionized formula computation by introducing functional programming concepts through Lambda Helper Functions (LHFs)—specifically MAP, REDUCE, and SCAN. These functions iterate across arrays dynamically, applying custom calculation logic to each element without requiring helper columns or Apps Script.
Understanding Lambda Helper Functions
Lambda Helper Functions take one or more arrays alongside a custom LAMBDA expression, applying calculation rules systematically:
- MAP: Transforms an array item-by-item, returning a new array of identical dimensions containing the calculated results.
- REDUCE: Iterates across an array and accumulates the values into a single summary scalar result (such as a compounded total or concatenated string).
- SCAN: Operates like REDUCE, but instead of returning only the final scalar result, it outputs an array displaying each intermediate accumulated step (ideal for running balances).
Using the MAP Function for Multi-Column Row Operations
Standard ARRAYFORMULA fails when evaluating row-by-row logical conditions like IF(AND(A2:A>10, B2:B="Active"), ...). MAP solves this by feeding corresponding elements from multiple columns into a row-level lambda:
The syntax for MAP is:
=MAP(array1, [array2, ...], LAMBDA(val1, [val2, ...], calculation))To evaluate customer status across Column A (Score) and Column B (Region) in rows 2 through 100:
=MAP(A2:A100, B2:B100, LAMBDA(score, region,
IF(AND(score >= 80, region = "West"), "Priority", "Standard")
))This single formula spills an array down the column, correctly evaluating the Boolean logic row-by-row.
Using the REDUCE Function for Custom Accumulations
The REDUCE function folds an entire array into a single accumulated metric:
=REDUCE(initial_value, array, LAMBDA(accumulator, current_value, calculation))Consider calculating compounded investment growth across variable annual interest rates listed in B2:B10 starting with an initial principal of $1,000:
=REDUCE(1000, B2:B10, LAMBDA(total, rate, total * (1 + rate)))Starting with 1000, REDUCE multiplies the accumulated balance by each successive interest rate in the array, returning the final compounded portfolio value in a single cell.
Using the SCAN Function for Running Balances
To display cumulative running totals or running bank balances that expand automatically as new transaction rows are logged:
=SCAN(initial_value, array, LAMBDA(accumulator, current_value, calculation))To generate an automatic running total of transaction amounts in C2:C50 starting from zero:
=SCAN(0, C2:C50, LAMBDA(runningTotal, tx, runningTotal + tx))Unlike REDUCE, SCAN spills an array showing the cumulative balance at every single transaction step. If new rows are appended to the table, the running balance updates automatically without dragging formulas.
Filtering and Combining Arrays Inside MAP
Nest Lambda Helper Functions with modern text utilities like REGEXREPLACE or TEXTSPLIT to sanitize messy datasets within a single reactive formula:
=MAP(A2:A50, LAMBDA(rawEmail,
LOWER(TRIM(REGEXEXTRACT(rawEmail, "^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+.[A-Za-z]{2,}$")))
))This extracts and lowercases verified email addresses from a column of raw customer notes, delivering enterprise data pipeline cleanliness in pure spreadsheet formulas.