Excel functions / Logical
MAP function
Runs a function on each value of an array and returns the results in the same shape.
Syntax
=MAP(array1, [array2], …, lambda)
Arguments
array: The array to go through. Several arrays of the same size are taken together.lambda: A LAMBDA with one input for each array.
When to use it
MAP runs a LAMBDA on every value of a range and spills the results in the same shape. It is for calculations that a plain formula cannot do value by value, such as a function that does not accept a range.
Examples
Each price with tax, rounded up to the next 5 cents
A B C 1 Price 2 1.2 1.45 3 8.9 10.70 4 2.4 2.90 In C2:
=MAP(A2:A4, LAMBDA(p, CEILING.MATH(p*1.2, 0.05)))→ 1.45; 10.70; 2.90A label for each score
A B C 1 Score 2 72 Pass 3 48 Retake In C2:
=MAP(A2:A3, LAMBDA(s, IF(s>=50, "Pass", "Retake")))→ Pass; Retake
Common mistakes
- When a plain formula would do. =A2:A4*1.2 already works value by value. MAP earns its place when the calculation needs a function that does not.
- Older versions of Excel. MAP and LAMBDA need Excel for Microsoft 365 or Excel 2024.
Practise it
Related functions
- LAMBDA: Makes a function of your own: names for its inputs, then the calculation.
- BYROW: Runs a function on each row of an array and returns one result per row.
- REDUCE: Boils an array down to one result by running a function on a running value and each item in turn.
- SCAN: Like REDUCE, but returns every step: a running total, a running maximum and the like.
- MAKEARRAY: Builds an array of a set size, working out each cell from its row and column number.