Excel MAP function (365)
The MAP function applies a LAMBDA function to create a new value and returns an array formed by mapping each value in the supplied array(s) to a new value.
The MAP function returns arrays of results.
- The MAP function is newly introduced in Excel for Microsoft 365. So it is not available in earlier versions of Excel. New dynamic array formulas are introduced in Excel for Microsoft 365, meaning no need to use Ctrl+ Shift+ Enter to enter the MAP formula as an array formula.
- The #VALUE! error value occurs if one of the below situations occur:
- an invalid LAMBDA function is provided;
- a wrong number of parameters is provided.
As the below screenshot shows, there is an array of data. To multiply the number in this array by 2 when it is larger than 100 and to square this number when it is smaller or equal to 100, please do as follows:
Please copy the formula below into cell E6, then press the Enter key to get the result.
=MAP (B6:C12,LAMBDA(x, IF(x>100, x*2,x*x)))
Excel BYROW Function
The Excel BYROW function applies a LAMBDA function to each row in a given array and returns the result per row as a single array.
Excel LAMBDA Function
The Excel LAMBDA function is used to create custom functions that can be reused throughout a workbook.
Excel MAKEARRAY Function
The Excel MAKEARRAY function returns a calculated array based on the given number of rows and columns.