Google Sheets MAP: Transform Arrays Row-by-Row with LAMBDA
MAP applies a custom LAMBDA function to every element in one or more arrays, producing an array of transformed results. Unlike ARRAYFORMULA, MAP works with functions like AND, OR, and complex custom logic.
=MAP(array1, [array2, ...], LAMBDA(val1, [val2, ...], expression))Technical Overview & Engine Mechanics
Formula Comparison: MAP vs. Alternatives
Formula Parameters & Arguments
| Argument | Type | Required | Description |
|---|---|---|---|
| array1 | Range | Required | The input range to map over. |
| LAMBDA | Function | Required | Lambda expression that processes each element. |
Real-World Spreadsheet Examples
Explore verified operational formulas used in enterprise reporting and database workflows:
Example 1: Evaluate Compound AND Condition Row-by-Row
=MAP(A2:A100, B2:B100, LAMBDA(status, spend, AND(status="Active", spend > 5000)))Example 2: Classify Customer Score Tiers
=MAP(A2:A100, LAMBDA(score, IFS(score >= 90, "Tier 1", score >= 70, "Tier 2", TRUE, "Standard")))Example 3: Calculate Distance / Coordinates Across Two Columns
=MAP(A2:A50, B2:B50, LAMBDA(lat, lon, SQRT(lat^2 + lon^2)))Example 4: Calculate Bonus Across Two Independent Arrays
=MAP(SalaryRange, PerformanceScoreRange, LAMBDA(salary, score, IF(score >= 4.5, salary * 0.20, salary * 0.05)))Example 5: URL Health Status Sanitizer
=MAP(A2:A100, LAMBDA(url, IF(ISURL(url), LOWER(TRIM(url)), "Invalid URL")))Common Errors & Solutions
Performance Pro Tips
- Use MAP whenever you need row-level logic involving AND(), OR(), or external lambda transformations.
Frequently Asked Questions: MAP
What is the difference between MAP and BYROW?
BYROW passes an entire row array to the lambda; MAP passes individual scalar cell values.
Can MAP accept three or four arrays?
Yes. You can supply multiple arrays as long as the LAMBDA declares a matching number of parameter variables.
Does MAP work with text strings?
Yes. MAP operates on numbers, strings, dates, and booleans.
How does MAP handle empty cells?
Empty cells are passed as blank values. Check for blanks with IF(val="", "", ...).
Is MAP supported in Excel 365?
Yes. Excel 365 supports identical MAP syntax.
Related Google Sheets Formulas
LAMBDA
LAMBDA allows users to author custom, reusable functions directly inside standard spreadsheet cells using native formulas, eliminating the need to write custom JavaScript in Google Apps Script.
Logic & Modern LambdasLET
LET assigns names to calculation results inside a formula. This stops Google Sheets from calculating the same sub-expression multiple times, boosting calculation performance by up to 500% and making long nested formulas easy to read.
Logic & Modern LambdasBYROW
BYROW evaluates a 2D range row by row, passing each horizontal row array to a LAMBDA function and returning a single vertical column of calculated summary values.