How to use the MAP function

What is the MAP function?

The MAP function is a new Excel 365 function that requires the LAMBDA function to work. It passes all values in an array to a LAMBDA function, which then calculates new values based on a formula you specify. It finally returns an array with the same size as the original array.

In other words, the process involves applying a specified formula to every element in an array or range. When multiple arrays are used, values are paired up based on their positions across the arrays. This pairing allows the formula to be applied to corresponding elements from each array. The following examples will illustrate this concept thoroughly.

1. Introduction

What is an array in Excel?

An array in Excel is a collection of values arranged in rows and columns. It can be thought of as a table or a grid of data.

There are two types of arrays. One-dimensional arrays: A single row or column of data. Two-dimensional arrays: Data organized in both rows and columns.

There are two types of array formulas: The first type returns a single value and the second type returns multiple values. An array formula is a formula that can perform multiple calculations on one or more sets of values.

Excel 365 subscribers have access to dynamic array formulas, a powerful feature that automatically adjusts its output range. These formulas populate the initial target cell and expand into neighboring cells as needed, adapting their size based on the formula's result. This automatic expansion and contraction of the output range is the key characteristic that gives them the name "dynamic" array formulas. The process of extending results into adjacent cells is known as "spilling.

Excel processes arrays in RAM allowing for rapid computations. However, when array sizes exceed available memory, Windows may resort to using virtual memory on the hard drive or SSD. This fallback to disk storage significantly slows down calculations, as accessing data from these devices is much slower than from RAM.

What is the LAMBDA function?

The MAP function uses the LAMBDA function as its second argument to define how each cell in the array should be calculated. This combination allows you to perform cumulative calculations across a range of values.

The LAMBDA function is required in the MAP function, you can't leave it out. Read more: LAMBDA function

2. Syntax

MAP(array_1, lambda_or_array<#>)

array_1	Required. A cell reference to a cell range or an array.
array_n	Optional. A cell reference to a cell range or an array.
lambda_or_array	Required. A LAMBDA function, it must be the last argument in the MAP function. Each array uses a different parameter in the LAMBDA function.

Back to top

3. Example 1

This example shows how to divide each number in the array B3:C11 by 2. The source data is in cell range B3:C11 and the result is displayed in cell range E3:F11.

Formula in cell E3:

This formula is an Excel 365 dynamic array formula meaning it spills values to adjacent cells as far as needed. This is a simple demonstration of the MAP function, I know that the formula can be made a lot smaller using only the division operator.

Explaining the formula

Step 1 - Build LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

The LAMBDA function is iterated as many times as there are values in cell range B3:C11.

LAMBDA(x,x/2)

The first argument specifies the parameter, the second argument lets you build a formula.

Step 2 - Pass values to the LAMBDA function

The MAP function lets you pass arrays to the LAMBDA function.

MAP(B3:C11, LAMBDA(x,x/2))

The first number is 771 in cell range B3:C11. 771/2 equals 385.5

It then continues with the remaining values returning an array with a size that matches the original array (B3:C11).

Back to top

4. MAP Function Example 2

This example demonstrates how to pass two arrays to the LAMBDA function. The LAMBDA function adds the first value in the first array B3:B11 to the first value in the second array. In other words, the calculations are based on the value's position in the array.

Formula in cell E3:

The first value in the first array named x (B3) is 771, the first value in the second array y (C3) is 637.  The LAMBDA function performs addition based on these values: 771 + 637 equals 1408.

The formula then continues to the next value pairs and continues to add the numbers. For example, the second value is 538 in the first array (B3:B11) and the second value in the second array (C3:C11) is 885. The formula returns 1423 in cell E4 based on 538 + 885 = 1423

Explaining the formula

Step 1 - Build the LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(x,y,x+y)

The two first parameters specifies which parameters to use, they correspond to the arrays in the MAP function.

x+y is the formula in the LAMBDA function.

Step 2 - Pass values to the LAMBDA function

MAP(B3:B11,C3:C11,LAMBDA(x,y,x+y))

x - B3:B11
y - C3:C11

x + y = B3:B11 + C3:C11

Back to top

Back to top

7. Two-way lookup using multiple tables

This example demonstrates how to perform lookups in multiple in multiple cross reference tables simultaneously using an Excel 365 LAMBDA function.

The first and second conditions are in cells D23 and D24 respectively. The cell references populates cells D19 to D21. The result is shown in cell D26, it spills values to cells below as far as needed.

Excel 365 formula in cell D26:

The formula returns a value for each cell reference, an #N/A error is displayed if at least one condition is not found.

Explaining formula

Step 1 - Convert string to cell reference

The INDIRECT function returns the cell reference based on a text string and shows the content of that cell reference.

Function syntax: INDIRECT(ref_text, [a1])

INDIRECT(a)

Step 2 - Get the first row

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(INDIRECT(a),1,0)

A 0 (zero) in the column argument lets you get all values in row 1. This is also true if you use a 0 (zero) in the row argument, however, this returns all values in a column in the specified cell range.

Step 3 - Match first condition

The MATCH function returns the relative position of an item in an array that matches a specified value in a specific order.

Function syntax: MATCH(lookup_value, lookup_array, [match_type])

MATCH(D23,INDEX(INDIRECT(a),1,0),0)

Step 4 - Get value in cross reference table based on first and second condition

The INDEX function returns a value or reference from a cell range or array, you specify which value based on a row and column number.

Function syntax: INDEX(array, [row_num], [column_num])

INDEX(INDIRECT(a),MATCH(D23,INDEX(INDIRECT(a),1,0),0),MATCH(D24,INDEX(INDIRECT(a),0,1),0))

The first condition is used in the horizontal lookup, the second condition is used in the vertical lookup.

Step 5 - Build LAMBDA function

The LAMBDA function build custom functions without VBA, macros or javascript.

Function syntax: LAMBDA([parameter1, parameter2, …,] calculation)

LAMBDA(a,INDEX(INDIRECT(a),MATCH(D23,INDEX(INDIRECT(a),1,0),0),MATCH(D24,INDEX(INDIRECT(a),0,1),0)))

The LAMBDA function is a requirement in order to use the MAP function in step 6.

Step 6 - Pass cell refs to LAMBDA function

The MAP function passes all values in an array to a LAMBDA function, the LAMBDA function calculates new values based on a formula you specify. It then returns an array with the same size as the original array.

Function syntax: MAP(array1, lambda_or_array<#>)

MAP(D19:D21,LAMBDA(a,INDEX(INDIRECT(a),MATCH(D23,INDEX(INDIRECT(a),1,0),0),MATCH(D24,INDEX(INDIRECT(a),0,1),0))))

The MAP function allows you to iterate or loop through each cell reference and return a corresponding value.

Back to top

8. Two-way lookup using multiple tables - User Defined Function

A User defined function is a custom function anyone can use, simply copy the VBA code and paste to your workbooks code module and you are good to go.

The custom function demonstrated in this article accepts an arbitrary number of range arguments (table ranges). The custom function returns the first match in each table if there is a match.

Array formula in cell C24:C26:

How to enter an array formula

1. Select cell range C24:C26
2. Copy above formula
3. Paste formula
4. Press and hold Ctrl + Shift
5. Press Enter once
6. Release all keys

User Defined Function Syntax

SEARCHMULTIPLETBL(xaxis, yaxis, cellrange1, [cellrange2])

Arguments

Parameter	Text
xaxis	Required. The x-axis condition you want to search for.
yaxis	Required. The y-axis condition you want to search for.
cellrange1	Required. The range you want to add.
[cellrange2]	Optional. You may have up to 255 additional argument cell ranges.

VBA Code:

'Name function and arguments
Function SEARCHMULTIPLETBL(xaxis As Variant, yaxis As Variant, ParamArray cellranges() As Variant)
'Declare variables and data types
Dim i As Integer, x As Variant, y As Variant
Dim temp() As Variant, xrange As Range, yrange As Range
'Redimension array variable temp so it may grow
ReDim temp(0)

'Iterate through all cell ranges
For i = LBound(cellranges) To UBound(cellranges)
    'Enable error handling
    On Error Resume Next
    'Save first row to xrange object
    Set xrange = cellranges(i).Rows(1)
    'Save first column to yrange object
    Set yrange = cellranges(i).Columns(1)
        'Find position of matching value in row and column
        x = Application.WorksheetFunction.Match(xaxis, xrange, 0)
        y = Application.WorksheetFunction.Match(yaxis, yrange, 0)
        'If found an error is not returned and Err.Number becomes 0 (zero)
        If Err.Number = 0 Then
            temp(UBound(temp)) = cellranges(i).Rows(y).Columns(x).Value
            ReDim Preserve temp(UBound(temp) + 1)
        Else
            'Disable error handling
            On Error GoTo 0
        End If
'Continue with next cell range
Next i
'Remove last container in array
ReDim Preserve temp(UBound(temp) - 1)
'Return values in temp array rearranged (transposed)
SEARCHMULTIPLETBL= Application.Transpose(temp)

End Function

Where to copy vba code?

1. Press Alt-F11 to open the Visual Basic Editor
2. Press with left mouse button on Module on the Insert menu
3. Copy and paste vba code.
4. Exit visual basic editor

'MAP' function examples

The following article has a formula that contains the MAP function.

Functions in 'Logical' category

The MAP function function is one of 16 functions in the 'Logical' category.

Excel function categories

Excel categories

Comments (5)