Excel MAP Function: Syntax, Examples & Tips

array:
The array or range of cells that you want to apply the lambda function to. This can be a single row, single column, or a two-dimensional array.

lambda:
A custom function defined using the LAMBDA syntax that specifies the operation to perform on each element of the array. This function can take parameters and return a value based on the input.

What is a MAP Formula in Excel?

The MAP function in Excel is a dynamic array function that allows you to apply a specified function to each element in an array or range. It evaluates each item in the input array and returns an array of results based on the function applied, making it a powerful tool for performing operations on multiple values simultaneously.

In simpler terms, the MAP function helps you manipulate and transform data across an array, making it easier to perform calculations and analyses without the need for complex formulas.

A Practical Example

Imagine you have a list of sales amounts in an Excel spreadsheet, and you want to apply a discount to each amount. Here’s the data you have:

Sales Data Table:

You want to apply a 10% discount to each sales amount.

MAP Formula

To calculate the discounted sales amounts, you would use the MAP formula as follows:

=MAP(A2:A6, LAMBDA(x, x * 0.9))

Breakdown of the Formula:

• A2:A6: This is the range of sales amounts that you want to apply the discount to.
• LAMBDA(x, x * 0.9): This is a LAMBDA function that defines the operation to be performed on each element (in this case, multiplying by 0.9 to apply a 10% discount).

Result of the Formula

When you apply the formula, it calculates the discounted sales amounts for each entry in the range. The output would be:

Here, the discounted amounts are calculated as follows:

• 200 * 0.9 = 180
• 150 * 0.9 = 135
• 300 * 0.9 = 270
• 250 * 0.9 = 225
• 100 * 0.9 = 90

Why Use MAP?

The MAP function is particularly useful when you need to perform the same operation on multiple values in an array without writing repetitive formulas. It allows you to efficiently transform data and can significantly simplify your calculations.

Key Takeaways:

• MAP: Applies a specified function to each element in an array or range.
• Dynamic Arrays: Returns an array of results based on the operation performed.
• Common Use Cases: Ideal for data transformations, applying calculations across ranges, and simplifying repetitive tasks.

Understanding how to use the MAP function can significantly enhance your data manipulation capabilities in Excel, enabling you to work more efficiently with your datasets.

Happy calculating!