# A to Z of Excel Functions: the MAP Function

20 December 2021

*Welcome back to our regular A to Z of Excel Functions blog. Today we look at the MAP function. *

** **

**The MAP function**

As one of the more recent Excel functions, **MAP** takes the honour of becoming **Excel’s 500 ^{th} function** – as agreed by fellow Excel Most Valuable Professional (MVP) Bill Jelen and ourselves.

You should be aware that Excel’s 500^{th} function *(see earlier)*, the **MAP **function, does not actually return a map!

Instead, it returns an array formed by mapping each value in the array(s) to a new value and applying a **LAMBDA** to create a new value accordingly. It has the following syntax:

**MAP(array1, lambda ***or ***array2, [lambda ***or ***array2, …])**

where:

**array1:**this is a required argument and represents the (first) array to be mapped**array2 and subsequent arrays:**these are optional arguments and represent additional arrays to be mapped**lambda:**this is a required argument which represents a**LAMBDA**which must be the final argument and must have a parameter for each array passed or another array to be mapped.

In short, **MAP** transforms values.

Let’s consider my Melbourne average temperatures data (in degrees Celsius):

If I want the year-on-year comparisons for each month where the average temperature is above 15 degrees Celsius, I can avoid using a “helper column” and instead use the formula

**=FILTER(Temps, BYROW(Temps, LAMBDA(year, AVERAGE(year) > 15)))**

Here, the **LAMBDA **function returns values where the average temperature for the year is above 15 degrees Celsius, using the **BYROW **function, *viz.*

The problem is, these temperatures have all been provided in Celsius, which those of you in the United States (amongst other countries) may not understand. If a US reader sees a temperature of 25 degrees, they will be breaking out the gloves, bobble hat and duffle coat, whereas us Aussies will be heading for the beach.

We need to convert – ** transform** – this data to Fahrenheit, so our US colleagues may better understand. All I need to do is wrap the above formula in a

**MAP**function:

**=MAP(FILTER(Temps, BYROW(Temps, LAMBDA(year, AVERAGE(year) > 15))), LAMBDA(temperature, IF(ISNUMBER(temperature), CONVERT(temperature, “C”, “F”), temperature)))**

**CONVERT(temperature, “C”, “F”) **simply converts the variable **temperature **from degrees Celsius to degrees Fahrenheit. This is wrapped in an **IF(ISNUMBER()) **check to ensure that we don’t try to convert text values (as this would cause an error): the **IF **statement leaves the value of **temperature **“as is” in this instance, and **LAMBDA **just wraps around all of this in order to declare the variable **temperature **“work”, so that **MAP **may do its work.

It’s true you could generate this result in stages, but the whole idea of these **LAMBDA **helper functions is to be able to create dynamic arrays in one fell swoop.

*We’ll continue our A to Z of Excel Functions soon. Keep checking back – there’s a new blog post every other business day.*

*A full page of the function articles can be found* *here. *