Skip to Content

How to use Excel MAP Function (With Examples)

The Excel MAP function lets you apply a LAMBDA calculation to each corresponding value in the given array or arrays.

For example, let’s assume that we are planning to award 10 additional marks in each subject to students who couldn’t score 40+ marks in the given subjects. Here is the snapshot of the data.

How to use Excel MAP Function (With Examples)

Our formula will be:

=MAP(B3:D12, LAMBDA(mark, IF(mark<40, mark + 10, mark)))

Where,

  • B3:D12 is the array including students’ marks in Maths, Science, and English subjects
  • LAMBDA(mark, IF(mark<40, mark + 10, mark)) is the LAMBDA formula to add 10 additional marks to those who scored below 40

Note that you cannot define multiple arrays in a single MAP formula until and unless each array is one-dimensional (having a single column or a single row). We will discuss this in more detail in later parts of this article.

Syntax

The MAP is a prebuilt logical function in Excel. Here’s the general syntax for the function:

=MAP(array, lambda_or_array2, …)

Where,

  • array arguments need to be replaced with the array or range
  • lambda_or_array2 is the second argument, where you can provide the second array, range, or the LAMBDA formula

Important Notes:

  • Input arrays must be one-dimensional (having a single column or a single row)
  • 2D array ranges are not supported
  • The function is not case-sensitive
  • All text values must be enclosed within double quotes
  • The function does not support wildcards

Click here to Learn Excel LAMBDA Function: An Ultimate tool to Create Custom Functions in Excel.

How to use the MAP Function in Excel

Download the example spreadsheet used to demonstrate the MAP function in the upcoming section. Practice is the key to success!

Download Now

Example 1: MAP Function Basics

Assume that you own an online store. You have a list of products with their prices and corresponding discounts as shown in the following image.

Our task is to find the final price after the discount. We will use the following formula.

Final Price = Original Price – (Original Price x Discount in Percentage)

Let us first create the above formula in Excel using the LAMBDA function. It would be as follows:

=LAMBDA(price, discount, price – (price * discount / 100))

Where,

  • price and discount are the parameters/arguments
  • price – (price * discount/100) is the actual formula to calculate the final price

If you wish to learn more about the LAMBDA function, click here: Excel LAMBDA Function: Ultimate tool to Create Custom Functions in Excel

Next, we will incorporate the above formula in the MAP function. It will apply that formula across the entire array to find the final price of all the given products in one click.

=MAP(B2:B11, C2:C11, LAMBDA(price, discount, price – (price * discount / 100)))

Where,

  • B2:B11 is the first one-dimensional array that includes product prices
  • C2:C11 is the second one-dimensional array, which includes a discount in percentage

Here, I am intentionally using the word one-dimensional. The MAP function does not support 2D arrays (B2:C5). It accepts a one-dimensional array (having a single column or a single row).

The results are displayed in the following GIF,

Example 2: MAP Function with Multiple Criteria

Let us assume that we want to monitor stock and set up restocking alerts. Our warehouse manager has sent us the following data.

Our task is to,

  • Generate a message stating that the given product needs to be restocked
  • Include a special warning if the given product is perishable
  • Make sure that the message is in uppercase only

We will use the LAMBDA function first to come up with a formula that satisfies the above condition. Note that we need to combine LAMBDA with other functions (IF, UPPER, and TEXTJOIN) as there are multiple conditions to be met.

=LAMBDA(fruit, stock, reorder, perishable,
     UPPER(TEXTJOIN(" – ", TRUE, fruit & ": " &
     IF(stock < reorder, "RESTOCK NEEDED", "SUFFICIENT STOCK"),
     IF(AND(stock < reorder, perishable = "Yes"), "URGENT: PERISHABLE ITEM", "")
     ))
)

Where,

  • fruit, stock, reorder, perishable are parameters/arguments
  • fruit & “: ” & IF(stock < reorder, “RESTOCK NEEDED”, “SUFFICIENT STOCK”) is the formula to generate the restocking message
  • IF(AND(stock < reorder, perishable = “Yes”), “URGENT: PERISHABLE ITEM”, “”) is the formula to extend the generated message if the item is perishable.
  • The UPPER function capitalizes and emphasizes the alert message
  • The TEXTJOIN function combines different messages with a dash separator

Next, we will incorporate the above formula in the MAP function.

=MAP(A2:A11, B2:B11, C2:C11, D2:D11,
   LAMBDA(fruit, stock, reorder, perishable,
   UPPER(TEXTJOIN(" – ", TRUE, fruit & ": " &
   IF(stock < reorder, "RESTOCK NEEDED", "SUFFICIENT STOCK"),
   IF(AND(stock < reorder, perishable = "Yes"), "URGENT: PERISHABLE ITEM", "")
   ))
 )
)

Where,

  • A2:A11 is the first one-dimensional array, which includes fruit names
  • B2:B11 is the second one-dimensional array, which includes stock
  • C2:C11 is the third one-dimensional array, which includes the reorder level
  • D2:D11 is the fourth one-dimensional array, which tells us whether the item is perishable

The results are displayed in the following GIF,

Here, I am using conditional formatting to highlight the products that need restocking.

Common Mistakes to Avoid while Using the MAP Function

The MAP is a powerful function. But it is a bit complex. Things get even worse when the dataset includes multiple columns and you need to deal with multiple criteria to solve the given problem.

So, there are certain scenarios where even an experienced Excel user can make mistakes.

  • Data Mismatch: Make sure the array includes numeric data while performing numeric calculations. Similarly, you need to ensure non-numeric values across the entire array while performing the logical test on text values.
  • One-dimensional Array: When multiple arrays are included in the formula, make sure they are one-dimensional (having a single row or a single column). The MAP function is not compatible with the 2D arrays (e.g., A2:B10).
  • The LAMBDA Function: The use of LAMBDA is a must when using the MAP function. Without a properly defined LAMBDA formula, the function won’t work as expected.
  • #SPILL Error: It occurs when the target array (column or row) already contains data. So, make sure to keep them empty. In some cases, the formula may overwrite the available data across the arrays.

Click here to Learn Excel LAMBDA Function: An Ultimate tool to Create Custom Functions in Excel.

Takeaway

The MAP is an array formula. So, by combining the MAP with the LAMBDA function, you can perform complex and lengthy tasks within a few seconds.

The function allows you to define multiple conditions or perform complex calculations within a single compact formula. It applies the LAMBDA formula to each cell of the given array. As such, any adjustments, including edits and new entries, automatically start reflecting across the new array.

Additional Resources:

  • Learn All Excel Logical Functions (With Examples)
  • LAMDA Function in Excel
  • AND Function in Excel
  • OR Function in Excel
  • NOT Function in Excel
  • XOR Function in Excel
  • Ultimate Guide to Comparison Operators in Excel
  • Excel Shortcuts List

Get an Office 365 Subscription to Access all the powerful Functions and Tools in Excel.