Use Excel MAP and LAMBDA to Search Matrices and Sum Matches
Question details
Search several matrices for a specified row and column and sum the matching values.

- 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.
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.
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.
Enter the row label to find in G1 and the column label to find in G2.
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)))))))
Change A1:E18, SEQUENCE(3), the six-row stride, and the INDEX offsets to match your matrix range, count, spacing, and dimensions.

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. Open the workbook: Open WPS Spreadsheets and use File > Open to select your Excel workbook.
- 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. 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. Adapt the ranges: Edit each INDEX and MATCH range for your matrices, and add another IFERROR lookup inside SUM for each additional matrix.

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.




