MAP_IJ Function for Excel

=MAP_IJ(array, LAMBDA(cell,i,j, expression) )

Argument	Description	Example
array	The array you want to use within MAP	A1:B3
LAMBDA	A lambda that uses the cell value and the corresponding i,j indices as parameters.

Description

MAP_IJ is just a convenient wrapper for MAP that gives you access to the row and column indices within your expression. MAKEARRAY is another function that gives access to indices, but may require you to do more array lookups than you want to.

MAP_IJ works by using MAP(array,iMat,jMat,LAMBDA(cell,i,j, expression )), where iMat is the row matrix and jMat is the column matrix. There are many ways to create the iMat and jMat matrices and I have tested the efficiency of multiple methods. So far, the fastest seems to be the use of MMULT which I implemented within MAP_IJ and the separate ROWMAT and COLMAT lambdas (code included below). ROWMAT(array) creates the iMat row matrix; COLMAT(array) creates the jMat column matrix.

Example: MAKEARRAY vs. MAP_IJ

In the example below, I am creating an upper triangular matrix by making all of the values in the lower-left half of the array zero. Method 1 uses MAKEARRAY which avoids having to compute the iMat and jMat matrices ahead of time, but requires an INDEX lookup. Method 2 and 3 are basically identical because Method 2 is what MAP_IJ is doing internally.

Method 1:
=MAKEARRAY( ROWS(array), COLUMNS(array), LAMBDA(i,j, IF(i>j,0,INDEX(array,i,j)) ) )

Method 2:
=MAP( array, ROWMAT(array), COLMAT(array), LAMBDA(v,i,j, IF(i>j,0,v) ))

Method 3:
=MAP_IJ( array, LAMBDA(v,i,j, IF(i>j,0,v) ))

An important thing to note is that MAKEARRAY will often use an INDEX lookup within the expression if you want to access values from an external array. In my experiments with the above example, all three methods performed nearly the same for different sizes of arrays.

In terms of efficiency, MAP_IJ appears to be about the same as MAKEARRAY. But, I suspect that there will be cases where one or the other method is more efficient.

Using ROWMAT and COLMAT with MAP

MAP_IJ is used when your primary data comes from a single array. To use MAP with multiple arrays, you can assemble the iMat and jMat matrices using ROWMAT and COLMAT. For example, if you were performing an element-wise function of 3 arrays, A, B and C, you could use MAP like this:

=MAP(A,B,C,ROWMAT(A),COLMAT(A),LAMBDA(a,b,c,i,j,  expression  ))

Row and Column Matrices

For a 4x3 array, here is what the iMat and jMat matrices look like. The Row matrix is the row numbers repeated across all columns. The Column matrix is the column numbers repeated across all rows.

=ARRAYTOTEXT(  ROWMAT(4,3),  1)
Result:
{1,1,1;
 2,2,2;
 3,3,3;
 4,4,4}
 
=ARRAYTOTEXT(  COLMAT(4,3),  1)
Result:
{1,2,3;
 1,2,3;
 1,2,3;
 1,2,3}

Note: ROWMAT and COLMAT can take an array as input and the result will be the index matrix for an array of the same size. For example, if array is 4x3, you can use =ROWMAT(array) or =ROWMAT(4,3).

Lambda Formula

This code for using MAP_IJ in Excel is provided under the License as part of the LAMBDA Library, but to use just this function, you may copy the following code directly into your spreadsheet.

Code for AFE Workbook Module (Excel Labs Add-in)

/**
* Like MAP for a single array, but with access to array indices
* Usage: =MAP_IJ(array, LAMBDA(cell,i,j, expression) )
*/
MAP_IJ = LAMBDA([array],[function],
IF(ISOMITTED(array),"MAP_IJ(array, LAMBDA(v,i,j,  fun_of_v_ij  ))",
LET(doc,"https://www.vertex42.com/lambda/map_ij.html",
    version,"1/2/2026 - Faster than the beta version",
    iMat,MMULT(SEQUENCE(ROWS(array)),SEQUENCE(,COLUMNS(array),1,0)),
    jMat,MMULT(SEQUENCE(ROWS(array),1,1,0),SEQUENCE(,COLUMNS(array))),
    res,MAP(array,iMat,jMat,function),
    res
)));

/**
* Create an mxn matrix of row numbers, or a matrix of row numbers the size of array m
* Usage: =ROWMAT(array) or =ROWMAT(4,3)
*/
ROWMAT = LAMBDA(m,[n],
LET(doc,"https://www.vertex42.com/lambda/map_ij.html",
    version,"1/2/2026 - Faster than the beta version",
    n,IF(ISOMITTED(n),1,n),
    IF( OR(ROWS(m)>1,COLUMNS(m)>1),
        MMULT(SEQUENCE(ROWS(m)),SEQUENCE(,COLUMNS(m),1,0)),
        MMULT(SEQUENCE(m),SEQUENCE(,n,1,0))
    )
));

/**
* Create an mxn matrix of column numbers, or a matrix of column numbers the size of array m
* Usage: =COLMAT(array) or =COLMAT(4,3)
*/
COLMAT = LAMBDA(m,[n],
LET(doc,"https://www.vertex42.com/lambda/map_ij.html",
    version,"1/2/2026 - Faster than the beta version",
    n,IF(ISOMITTED(n),1,n),
    IF( OR(ROWS(m)>1,COLUMNS(m)>1),
        MMULT(SEQUENCE(ROWS(m),1,1,0),SEQUENCE(,COLUMNS(m))),
        MMULT(SEQUENCE(m,1,1,0),SEQUENCE(,n,1,1))
    )
));

Disclaimer: This article is meant for educational purposes only. See the License regarding the LAMBDA code, and the site Terms of Use for the documentation.

MAP_IJ, ROWMAT, COLMAT

Description

Using ROWMAT and COLMAT with MAP

Lambda Formula

Code for AFE Workbook Module (Excel Labs Add-in)

Follow Us On ...