Logic & Modern Lambdas FormulaGoogle Sheets V8 Engine Verified

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.

Formula Syntax:=MAP(array1, [array2, ...], LAMBDA(val1, [val2, ...], expression))
💡 Best Practice: Always try complex formulas on a copy of your file first before applying them to active production data.

Technical Overview & Engine Mechanics

The MAP function applies a LAMBDA calculation to every value in one or more arrays and returns an array of the results. Unlike ARRAYFORMULA which evaluates ranges as entire vectors, MAP iterates cell-by-cell or row-by-row across corresponding coordinates of multiple arrays simultaneously. This enables multi-array operations that standard array formulas cannot perform.

Formula Comparison: MAP vs. Alternatives

Compared to ARRAYFORMULA, MAP supports functions that don't natively vectorize (like custom Named Functions and conditional logic). Compared to BYROW, MAP can accept multiple arrays of identical dimensions and map their elements together.

Formula Parameters & Arguments

ArgumentTypeRequiredDescription
array1RangeRequiredThe input range to map over.
LAMBDAFunctionRequiredLambda 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)))
How it works: Solves the classic limitation where `AND()` does not work inside standard ARRAYFORMULA.

Example 2: Classify Customer Score Tiers

=MAP(A2:A100, LAMBDA(score, IFS(score >= 90, "Tier 1", score >= 70, "Tier 2", TRUE, "Standard")))
How it works: Applies multi-tier customer categorization down an entire column.

Example 3: Calculate Distance / Coordinates Across Two Columns

=MAP(A2:A50, B2:B50, LAMBDA(lat, lon, SQRT(lat^2 + lon^2)))
How it works: Computes multi-column mathematical equations cleanly.

Example 4: Calculate Bonus Across Two Independent Arrays

=MAP(SalaryRange, PerformanceScoreRange, LAMBDA(salary, score, IF(score >= 4.5, salary * 0.20, salary * 0.05)))
How it works: Evaluates two column ranges in parallel and computes individual bonus allocations.

Example 5: URL Health Status Sanitizer

=MAP(A2:A100, LAMBDA(url, IF(ISURL(url), LOWER(TRIM(url)), "Invalid URL")))
How it works: Maps across links to sanitize and validate addresses.

Common Errors & Solutions

❌ #VALUE! Arrays must have identical dimensions
Resolution: All input ranges passed into MAP must have the exact same row count.
❌ #VALUE! Array dimension mismatch in MAP
Resolution: All arrays supplied to MAP must have identical row and column counts.
❌ Lambda parameter count mismatch
Resolution: The number of parameters in the LAMBDA must match the number of arrays passed to MAP (e.g., 2 arrays require LAMBDA(a, b, ...)).

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