MAP function

MAP applies a custom LAMBDA to each element of a range, returning a new array of the same shape with the calculated results.

=MAP(array1, lambda)

Generate a MAP formula

Describe what you need. The generator will reach for MAP where MAP is the right tool, and tell you when it is not.

How to get a better answer
  • Name your columns by letter and by header: "column F (Net Value)" beats "the amount column".
  • State every condition, including the negatives — "not cancelled" changes the formula's shape.
  • Say where the data starts if it is not row 1, and whether it will grow.
  • Check the settings above match your spreadsheet: the wrong argument separator is a syntax error on your machine.

Arguments

How MAP reads its arguments
array1requiredlambdarequiredMAP →
ArgumentRequiredDescription
array1RequiredA range or array of any size; MAP will iterate over each cell, and empty cells are passed to the LAMBDA as empty strings.
lambdaRequiredA LAMBDA that receives a single argument (the current element) and must return a value; if it returns an array, MAP will raise #VALUE!.

Returns

It returns a spill array whose dimensions match the input array.

Availability

Excel: 365 · Google Sheets: Supported

Worked examples

1. Convert SKU codes to lowercase

A001
A002
A003
A004
=MAP(A2:A5, LAMBDA(s, LOWER(s)))

Result: a001a002a003a004

The SKU column (A2:A5) contains four text strings. MAP feeds each string to the LAMBDA, which calls LOWER to produce a lowercase version. The function spills a four-row single-column array that mirrors the input shape.

2. Increase each item’s cost by 10 %

5.00
7.50
3.25
12.00
=MAP(E2:E5, LAMBDA(c, ROUND(c*1.10,2)))

Result: 5.508.253.5813.20

The Cost column (E2:E5) holds numeric values. MAP passes each cost to the LAMBDA, which multiplies by 1.10 and rounds to two decimals. The resulting array spills in the same four-row layout, giving the new, higher prices.

3. Flag items that are below a static safety stock level

120
80
50
200
=MAP(C2:C5, LAMBDA(q, IF(q<100, "Low", "OK")))

Result: OKLowLowOK

Column C lists On-Hand quantities. The LAMBDA checks each quantity against the threshold of 100. If the quantity is less, it returns the text "Low"; otherwise it returns "OK". MAP returns a column of status flags that line up with the original rows.

Common errors

Which MAP error are you seeing?
MAP returned an error#SPILL!
Clear the cells where the result will spill, or move the formula to a location with enough empty space.
#VALUE!
Make the LAMBDA return a single value—use aggregation functions like SUM or INDEX to collapse the inner array.
#CALC!
Rewrite the LAMBDA to be non-recursive or replace the logic with a built-in iterative function such as REDUCE or SCAN.
ErrorWhy it happensHow to fix it
#SPILL!The destination area for MAP’s result already contains data, so the spill cannot expand.Clear the cells where the result will spill, or move the formula to a location with enough empty space.
#VALUE!The LAMBDA returns an array (e.g., using SEQUENCE) instead of a single scalar, which MAP cannot embed within each element.Make the LAMBDA return a single value—use aggregation functions like SUM or INDEX to collapse the inner array.
#CALC!The LAMBDA calls itself recursively without a termination condition, causing a calculation overflow.Rewrite the LAMBDA to be non-recursive or replace the logic with a built-in iterative function such as REDUCE or SCAN.

Tips and when to use something else

  • Use MAP when you need a one-to-one transformation of each cell; for row-wise or column-wise reductions, try BYROW or BYCOL.
  • Combine MAP with LET to define intermediate constants, keeping formulas readable and performant.
  • If your calculation depends on multiple columns, consider BYROW with a LAMBDA that receives an entire row as its argument.
  • When you need to look up values instead of transforming them, XLOOKUP or VLOOKUP is usually a better fit.

Related functions

LAMBDALAMBDA lets you create custom, reusable functions directly in a cell, returning any value or array based on supplied parameters.BYROWBYROW applies a LAMBDA to each row of a range and returns a vertical array of the results, enabling row-wise calculations in Excel 365 and Google Sheets.REDUCEREDUCE iteratively applies a LAMBDA to each element of an array, carrying forward an accumulator that starts with the initial value.IFThe IF function returns one value if a condition is true and another (or nothing) if it is false, letting you branch logic in a cell.VLOOKUPVLOOKUP searches the first column of a table and returns a value from a specified column in the same row, optionally using exact or approximate matching.

Frequently asked questions

Can MAP be used to concatenate values from two separate columns?
Not directly with the two-argument signature. MAP only accepts one array, so you would need to combine the columns first (e.g., with CHOOSECOLS) or use BYROW, which passes an entire row to the LAMBDA.
Why does MAP return a #SPILL! error even though the target cells look empty?
Excel treats cells that contain formulas returning arrays as occupied, even if they appear blank. Ensure the entire spill range is truly empty, or move the MAP formula to a fresh area.
Is MAP available in older versions of Excel?
No. MAP is part of the dynamic-array functions introduced in Excel 365 and Excel for the web. Legacy desktop versions without dynamic arrays cannot evaluate MAP.
How does MAP differ from BYROW when I only need to change one column?
MAP processes each cell individually, preserving the original layout, while BYROW processes whole rows at a time. If you only need a cell-by-cell transformation, MAP is simpler; for row-level logic, BYROW is more appropriate.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17