This guide will discuss how to use MAPfunction in Google Sheets.
When we want to map each value in a given array to a new value using the LAMBDA function, we can easily do this using the MAP function in Google Sheets.
The rules for using the MAP function in Google Sheets are the following:
- The
LAMBDAfunction must have exactly 1 name argument for each array passed. - The formula_expression argument in the
LAMBDAfunction must use the names inputted in the name argument. - The given LAMBA function must accept the same number of name arguments as the number of input arrays given to the
MAPfunction. Otherwise, the function will return an #N/A error.
Google Sheets released the LAMBDA function alongside a few Lambda helper functions (LHFs) which can be used to create more complex LAMBDA formulas.
Some Lambda helper functions released are the MAPfunction, REDUCE function, BYCOL function, BYROW function, SCAN function, and MAKEARRAY function.
The BYCOL, BYROW, SCAN, and REDUCE functions can only take a single array or range. However, the MAKEARRAY function takes only the row and column numbers to create a calculated array.
The MAP function is the only LAMBA helper function that takes multiple arrays. Hence, it will take various name arguments within the LAMBDA function.
In this guide, we will provide a step-by-step tutorial on how to use the MAP function in Google Sheets. Additionally, we will explore the syntax and a real example of using the function.
Great! Let’s dive right in.
The Anatomy of the MAP Function
The syntax or the way we write the MAP function is as follows:
=MAP(array1,[array2,...],LAMBDA)
- = the equal sign is how we start any function in Google Sheets.
- MAP() refers to our
MAPfunction. This function creates an array of data from a given range, where each value is mapped to a new value based on a customLAMBDAfunction. - array1 is a required argument. This refers to an array or range we want to map into a new array.
- array2 is an optional argument. This refers to any additional arrays or ranges we also want to map into a new array.
- LAMBDA is a required argument. This is the
LAMBDAfunction that will be mapped to each value in the given array to get a new value. The name argument must have the same number of arrays to map each value.
A Real Example of Using MAP Function in Google Sheets
In our previous guide about using the LAMBDA function in Google Sheets, we have a data set containing the salaries received by an employee each month.
Our initial data set would look like this:
The spreadsheet above shows the months, the salary received in each month, and the total salary. We used the LAMBDA function to calculate 40% of the total salary.
Now we want to calculate 40% of the salary for each month. To do this, we will simply put our LAMBDA function inside the MAP function, which is a Lambda helper function.
The LAMBDA function we will be using is:
=LAMBDA(money,money*0.4)
The formula above uses money as a name argument to refer to the salary value. Then, the formula_expression multiplies money (the salary value) by 0.40 to get 40% of the salary.
We need to input the LAMBDA function to calculate 40% of the salary each month to all values in the given range. Then, we can do this by using the MAP function together with the LAMBDA function.
The final formula would be:
=MAP(B2:B10,LAMBDA(money,money*0.4))
The MAP function will calculate our LAMBDA function, which is money*0.4 to each value in the B1:B10 array. Then, it will put the results into an output array of the same dimensions as the given array.
Our final data set would look like this:
You can make your own copy of the spreadsheet above using the link below.
Creating a Named Function
A Named function allows us to create and reuse custom functions across Google Sheets.
We have created a custom formula using the LAMBDA function and placed our LAMBDA inside a MAP function. Thus, we can use this formula to create a Named function.
Once we input our MAP formula containing the LAMBDA function, an option will appear below to allow us to add a new function.
When we select this option, it will bring us to a side menu. This menu will let us name our custom function to anything we want and input a description.
In this example, we will name it the SAVINGS function since it will calculate 40% of the salary for savings.
Once created, we can reuse this custom function any time we want by simply using our chosen name.
Amazing! Now we can dive into the steps of using the MAP function in Google Sheets.
How to Use MAP Function in Google Sheets
1. First, we will select an empty cell to display the result. Then, we will start our formula by typing an equal sign and the function name. Our starting formula would be “=MAP(“.
2. We will select the array or range containing the values we want to map. In this case, we will select the range B2:B10. This will make our formula “=MAP(B2:B10”.
3. Next, we will input the LAMBDA function within our MAP function. To do this, we will simply type in the name of the function to give us the formula “=MAP(B2:B10,LAMBDA(”.
4. We will type in our custom LAMBDA formula, which calculates 40% of the salary. In this case, our final formula would be “=MAP(B2:B10,LAMBDA(money,money*0.4))”.
5. Lastly, we will press the Enter key to return the result.
6. Additionally, we can convert this formula into a Named function. If you want to be able to reuse this formula by simply using a specific name, click the option below Add new function.
7. In the side menu, we can input the name and description of our formula. In this case, we will name it “SAVINGS” and input the appropriate description.
8. We will select the Next option.
9. Finally, we will click the Create option to create our new Savings() function.
And tada! We have successfully used the MAP function in Google Sheets.
You can apply this guide whenever you need to perform more advanced array operations with your LAMBDA function. You can now use the MAP function and the various other Google Sheets formulas available to create great worksheets that work for you.
FAQs:
1. I got a #N/A error when using the MAP function. What should I do?
A #N/A error means that the given LAMBDA function does not have the same number of name arguments as the given array or range in the MAP function.
For example, let’s say your formula is: =MAP(B2:B10,C2:C10,LAMBDA(money,money*0.4))
In the formula above, you selected two arrays (B2:B10,C2:C10), but you only have one name argument in your LAMBDA function, which is money.
The correct formula must be:
=MAP(B2:B10,C2:C10,LAMBDA(money,salary,money*0.4))
Now you have two arrays (B2:B10,C2:C10) and two name arguments (money,salary).
That’s pretty much it! We will be doing more guides on how to use other Lambda helper functions. Make sure to subscribe to our newsletter to be the first to know about the latest guides and tutorials from us.
