Function Problems

Use Excel MAP and LAMBDA to Search Matrices and Sum Matches

Bushra ParveenBushra Parveen Nov 18, 2026 869 views

Question details

Search several matrices for a specified row and column and sum the matching values.

Search Multiple Excel Matrices and Sum Matches with MAP and LAMBDA
Product
Microsoft Excel
Device & OS
not provided
Scenario
The user has multiple matrices in a structured range and wants to calculate a combined result for matching row and column labels.
Observed behavior
The desired result is the sum of matching matrix values, with missing matches contributing zero.
Before you start

Check that your Excel version supports MAP, LAMBDA, LET, SEQUENCE, and XMATCH. Arrange the matrices with consistent dimensions and spacing, and ensure each matrix has unique row and column labels.

Solution 1Recommended

Use MAP to Look Up and Sum Values in Stacked Matrices

Use this formula when matrices have the same layout and are stacked at regular row intervals.

The example uses three 4-by-4 matrices in A1:E18. The first matrix has column labels in B1:E1, row labels in A2:A5, and values in B2:E5. The next blocks begin at rows 7 and 13, giving a six-row stride. Rows 6, 12, and 18 are unused.

MAP processes each matrix index, XMATCH locates the requested labels, and INDEX returns the corresponding value. SUM combines the results; a missing label contributes zero.

1
Enter the lookup labels

Enter the row label to find in G1 and the column label to find in G2.

2
Enter the formula

Select an empty cell and enter: =LET(rng,$A$1:$E$18,rowKey,$G$1,colKey,$G$2,SUM(MAP(SEQUENCE(3),LAMBDA(k,LET(base,(k-1)*6,rr,IFNA(XMATCH(rowKey,INDEX(rng,base+2,1):INDEX(rng,base+5,1)),0),cc,IFNA(XMATCH(colKey,INDEX(rng,base+1,2):INDEX(rng,base+1,5)),0),IF(OR(rr=0,cc=0),0,INDEX(rng,base+1+rr,1+cc)))))))

3
Adjust the layout

Change A1:E18, SEQUENCE(3), the six-row stride, and the INDEX offsets to match your matrix range, count, spacing, and dimensions.

Use MAP to Look Up and Sum Values in Stacked Matrices
Matching and errors: XMATCH returns the first matching label in each matrix. The formula handles missing labels, but errors in matched matrix values can still propagate.
Work with matrices in WPS Spreadsheets

Sum Matching Matrix Values in WPS Spreadsheets

Use INDEX and MATCH in WPS Spreadsheets to look up a row and column in each matrix, then sum the results without relying on MAP or LAMBDA.

  1. 1. Open the workbook: Open WPS Spreadsheets and use File > Open to select your Excel workbook.
  2. 2. Set up the example: Place three 4-by-4 matrices in A1:E18, with column headers on rows 1, 7, and 13 and row headers in A2:A5, A8:A11, and A14:A17. Enter the row lookup label in G1 and the column lookup label in G2.
  3. 3. Sum the lookups: Select an empty cell and enter: =SUM(IFERROR(INDEX(B2:E5,MATCH(G1,A2:A5,0),MATCH(G2,B1:E1,0)),0),IFERROR(INDEX(B8:E11,MATCH(G1,A8:A11,0),MATCH(G2,B7:E7,0)),0),IFERROR(INDEX(B14:E17,MATCH(G1,A14:A17,0),MATCH(G2,B13:E13,0)),0))
  4. 4. Adapt the ranges: Edit each INDEX and MATCH range for your matrices, and add another IFERROR lookup inside SUM for each additional matrix.
Search stacked matrices by row and column labels.Use familiar INDEX and MATCH formulas for each matrix.Open and edit Microsoft Excel XLSX and XLS files.Work with a familiar spreadsheet interface.
microsoft office alternative - wps office

Frequently Asked Questions

What happens if a label is missing from one matrix?

In the MAP formula, IFNA converts a missing XMATCH result to zero, so that matrix contributes zero to the sum.

How do I search more than three matrices?

Expand the range in LET to include all blocks and change SEQUENCE(3) to the number of matrices. Keep the stride and offsets aligned with the layout.

Can I calculate an entire result matrix instead of one value?

Yes. Use MAKEARRAY to generate output cells and put the lookup-and-sum logic inside its LAMBDA, using the output row and column indexes to select the labels to search.

Why does the WPS formula use IFERROR?

IFERROR returns zero when a lookup fails, allowing SUM to continue. It also masks other errors in that lookup, so check the source ranges if a result is unexpectedly zero.

Do the matrix labels have to be unique?

For the examples, use unique row and column labels within each matrix. XMATCH and MATCH return the first match, not the sum of duplicate labels.