How to Use the MAP and LAMBDA Functions in Google Sheets to Transform Multiple Ranges Simultaneously

Automating row-by-row and cell-by-cell data manipulation in Google Sheets traditionally required dragging formulas down hundreds of rows or writing cumbersome ARRAYFORMULA expressions that frequently broke when paired with logical operators like AND and OR. With the integration of MAP and LAMBDA, Google Sheets provides a modern, functional approach to transform multiple ranges concurrently within a single, dynamic array formula.

What is the MAP Function in Google Sheets?

The MAP function belongs to the Lambda helper function family. It takes one or more arrays or ranges and applies a custom calculation (defined by LAMBDA) to each corresponding element, spilling the transformed results across an identical output grid.

The syntax for MAP is structured as follows:

=MAP(array1, [array2, ...], LAMBDA(name1, [name2, ...], formula_expression))

Where:

• array1, array2, ...: The ranges or arrays you wish to evaluate. If passing multiple ranges, they must share the exact same row and column dimensions.
• LAMBDA: The container function that accepts parameter placeholders and contains the calculation logic.
• name1, name2, ...: Variable names assigned to represent individual elements drawn from array1, array2, etc.
• formula_expression: The mathematical or logical evaluation applied to the variables.

Single Array Example: Formatting and Clean-up

Suppose you have raw customer names in column A (range A2:A50) containing erratic leading spaces and irregular casing. To trim extra whitespace and convert every name to proper title case using a single formula, place this in cell B2:

=MAP(A2:A50, LAMBDA(name, PROPER(TRIM(name))))

Unlike classic formulas that must be manually copied downward, MAP processes each cell in A2:A50 individually and spills clean, formatted strings down column B.

Multi-Range Mapping: Calculating Conditional Scores

The true power of MAP shines when evaluating multiple disparate columns simultaneously. Consider a gradebook where:

• Column B: Exam Score (0 to 100)
• Column C: Attendance Rate (0% to 100%)

To pass, a student must achieve an Exam Score of 70 or higher and an Attendance Rate of at least 80%. In traditional Sheets, ARRAYFORMULA(AND(B2:B50>=70, C2:C50>=0.8)) fails because AND aggregates the entire array into a single boolean. With MAP, each row is evaluated independently:

=MAP(B2:B50, C2:C50, LAMBDA(score, attendance, IF(AND(score>=70, attendance>=0.8), "Pass", "Needs Review")))

Because the LAMBDA receives individual scalar variables for each row iteration, functions like AND, OR, and IFS execute flawlessly without collapsing the dataset.

Comparing MAP with Other Lambda Helper Functions

Function	Primary Purpose	Output Shape
MAP	Transforms elements across one or more identical ranges element-by-element	Matches original dimensions (M × N)
BYROW	Iterates row by row, aggregating each entire row	Single column array (M × 1)
BYCOL	Iterates column by column, aggregating each entire column	Single row array (1 × N)
SCAN	Accumulates running totals or running state across array values	Matches original dimensions (M × N)
REDUCE	Combines an entire array into a single cumulative scalar result	Single cell (1 × 1)

Advanced Use Case: Dynamic Price Calculation with Tiered Discounts

Suppose you have Base Prices in range D2:D100 and Order Quantities in range E2:E100. You want to apply tiered discount logic:

• Quantities under 10 receive 0% discount.
• Quantities between 10 and 49 receive 10% discount.
• Quantities of 50 or more receive 20% discount.

Input the following MAP formula into cell F2:

=MAP(D2:D100, E2:E100, LAMBDA(price, qty,
  LET(
    discountRate, IFS(qty >= 50, 0.20, qty >= 10, 0.10, TRUE, 0),
    grossTotal, price * qty,
    grossTotal * (1 - discountRate)
  )
))

Here, combining MAP with LET creates readable, self-documenting code directly inside your spreadsheet cell, producing robust calculations that dynamically accommodate changes across both source ranges.