How to Build Dynamic Two-Dimensional LAMBDA Arrays in Excel
Question details
The user wants to generate a two-dimensional array across a variable number of cases and years using age, sex, and mortality data, but faces the limitation that native formulas cannot return an array of arrays.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Calculating complex mortality and survival rates across multiple subjects and years requiring a dynamic 2D matrix.
- Observed behavior
- Excel does not natively support returning an array of arrays directly from standard functions, requiring structural workarounds using MAKEARRAY, MAP, or REDUCE to generate the final matrix.
Ensure you are using a modern version of Excel or WPS Office that fully supports dynamic array functions (such as LAMBDA, MAKEARRAY, and MAP). Verify that your base data and mortality tables are cleanly formatted.
Use the MAKEARRAY Function to Construct the Matrix directly
MAKEARRAY is the most direct solution for building a 2D matrix when the source data boundaries (rows and columns) are known.
Since standard array outputs cannot nest arrays natively, MAKEARRAY iterates row-by-row and column-by-column to calculate values dynamically, populating a structured grid.
Identify the number of rows from your source cases (e.g., A7:B14 for sex and age) and the number of columns (years) from your parameters.
Begin your formula with '=LET(data, A7:B14, MAKEARRAY(ROWS(data), B4, LAMBDA(r, c, ...)))' to establish the framework of the grid.
Inside the LAMBDA function, define variables to pull the age and sex for the current row using '=INDEX(data, r, 2)' for age and '=INDEX(data, r, 1)' for sex.
Add your calculation logic, such as an IF statement to check against maximum limits and custom functions to calculate survival differences based on the column index (c).
Broadcast Fields Using the MAP Function
By broadcasting age and sex values across the desired two-dimensional layout, you can map an XLOOKUP-based LAMBDA across the entire resulting matrix.
Define a Reusable Custom LAMBDA using SUMIFS
If you unpivot your underlying mortality table, you can create a reusable named LAMBDA function in the Name Manager to spill the results dynamically.
Build Complex Dynamic Arrays Easily with WPS Spreadsheet
WPS Office fully supports advanced dynamic array formulas including LAMBDA, MAKEARRAY, MAP, and REDUCE. You can seamlessly construct complex 2D data matrices with zero compatibility issues.
- 1. Open your data in WPS Spreadsheet: Launch WPS Office, open the Spreadsheet module, and load your workbook containing the mortality tables.
- 2. Select your spill range: Click on the top-left cell of the intended output area where you want your two-dimensional array to generate.
- 3. Input the formula: Type out your =MAKEARRAY or =MAP formula and press Enter. The dynamic 2D array will calculate and spill automatically.
Frequently Asked Questions
Why does Excel return a #CALC! error when trying to return arrays within arrays?
Nested arrays (an array of arrays) are not natively supported in standard Excel or WPS Office dynamic array outputs. You must use specialized functions like MAKEARRAY, REDUCE, or VSTACK to flatten or construct a single unified 2D matrix output.
What is the benefit of using MAKEARRAY over REDUCE for 2D matrices?
MAKEARRAY is generally more efficient and straightforward to read when the exact dimensions (number of rows and columns) of the final 2D array are known in advance. REDUCE is better suited for accumulating and stacking items of varying lengths.
Are custom LAMBDA formulas fully compatible between Excel and WPS Office?
Yes, modern versions of WPS Office have built-in support for dynamic array functions, including LAMBDA. Formulas defined in the Name Manager or written in cells will work seamlessly when switching between platforms.
