How to Build Dynamic Multi-Criteria Sensitivity Tables with MAKEARRAY, CHOOSEROWS, and LAMBDA in Excel

In quantitative finance, private equity underwriting, and corporate FP&A (Financial Planning & Analysis), sensitivity analysis is the cornerstone of risk modeling. Investment committees and chief financial officers rarely evaluate a single base-case financial projection; they demand two-dimensional scenario matrices evaluating how asset valuation, Net Present Value (NPV), or Internal Rate of Return (IRR) fluctuate when critical assumptions—such as discount rates, exit multiples, or revenue growth rates—shift across plausible ranges.

Historically, financial modelers relied on Excel’s legacy What-If Data Tables ({=TABLE(row_input, col_input)}). However, legacy data tables present crippling limitations: they are single-threaded, highly volatile (forcing constant workbook recalculation pauses), cannot be nested inside dynamic arrays, and fail when referenced in modern web or automated reporting environments. With modern Microsoft 365 dynamic array engines, analysts can build lightning-fast, fully responsive multi-criteria sensitivity tables using MAKEARRAY, LAMBDA, and LET.

Architectural Overview: Legacy What-If Tables vs. Dynamic MAKEARRAY Grids

Comparing the underlying calculation architecture highlights why financial engineers are abandoning legacy data tables:

Evaluation Factor	Legacy What-If Data Tables ({=TABLE})	Dynamic Array Engine (MAKEARRAY + LAMBDA)
Threading Architecture	Single-threaded; serial recalculation blocks Excel UI.	Multi-threaded calculation (MTC); parallelizes across multi-core CPUs.
Spill Behavior	Fixed range array; requires manual formula dragging and grid resizing.	Native dynamic spill; expands or contracts automatically when inputs change.
Calculation Mode	Often requires setting Excel to “Automatic except for data tables” to prevent lag.	Standard automatic calculation; zero sluggishness on multi-thousand cell matrices.
Self-Contained Output	Headers and formatting must be manually aligned around the table periphery.	Combines headers, labels, and computed matrices into a single unified formula using VSTACK and HSTACK.

Core Formulation: The Anatomy of MAKEARRAY and LAMBDA

The MAKEARRAY function synthesizes a calculated grid of dimensions r by c, passing the current row and column indices into a custom LAMBDA function:

=MAKEARRAY(rows, columns, LAMBDA(row_index, col_index, calculation))

By using INDEX inside the LAMBDA, Excel extracts the specific sensitivity variables corresponding to row r and column c, injecting them directly into the financial valuation formula.

Step-by-Step Implementation: Building an NPV Sensitivity Matrix

Consider an enterprise investment model where we must stress-test Net Present Value (NPV) across five Discount Rates (Columns) and five Terminal Growth Rates (Rows).

Step 1: Layout the Model Inputs

• Cell B1: Initial Investment Outlay = -£1,000,000
• Cells B2:F2: 5-Year Cash Flow Projections = £250,000 | £300,000 | £350,000 | £400,000 | £450,000
• Range A6:A10 (Row Input Vector: Growth Rates): 1.0%, 1.5%, 2.0%, 2.5%, 3.0%
• Range B5:F5 (Column Input Vector: Discount Rates): 8.0%, 9.0%, 10.0%, 11.0%, 12.0%

Step 2: Construct the Pure MAKEARRAY Calculation Formula

In cell B6, enter the following dynamic array formula to populate the entire 5×5 sensitivity matrix:

=LET(
    row_vars, A6:A10,
    col_vars, B5:F5,
    cash_flows, B2:F2,
    initial_outlay, B1,
    MAKEARRAY(
        ROWS(row_vars),
        COLUMNS(col_vars),
        LAMBDA(r, c,
            LET(
                g, INDEX(row_vars, r),
                d, INDEX(col_vars, c),
                base_npv, NPV(d, cash_flows) + initial_outlay,
                terminal_val, (INDEX(cash_flows, 5) * (1 + g)) / (d - g),
                disc_terminal, terminal_val / ((1 + d) ^ 5),
                ROUND(base_npv + disc_terminal, 0)
            )
        )
    )
)

Upon pressing Enter, the formula calculates all 25 financial scenarios instantly and spills cleanly across range B6:F10.

Advanced Pattern: Building a Fully Self-Contained Matrix with VSTACK and HSTACK

Instead of manually typing header rows and column labels, construct an entire presentation-ready sensitivity matrix—complete with dynamic headers and corner labels—from a single cell formula.

=LET(
    row_labels, {"1.0%"; "1.5%"; "2.0%"; "2.5%"; "3.0%"},
    col_labels, {"8.0%", "9.0%", "10.0%", "11.0%", "12.0%"},
    grid, MAKEARRAY(ROWS(row_labels), COLUMNS(col_labels),
        LAMBDA(r, c,
            LET(
                g, NUMBERVALUE(INDEX(row_labels, r)),
                d, NUMBERVALUE(INDEX(col_labels, c)),
                ROUND((1000000 * (1 + g)) / (d - g), 0)
            )
        )
    ),
    top_header, HSTACK("Terminal Growth \ Discount Rate", col_labels),
    body, HSTACK(row_labels, grid),
    VSTACK(top_header, body)
)

This single formula generates a pristine 6×6 reporting grid. If row or column assumptions change, the entire table and its labels re-index without breaking formula links.

Applying Dynamic Conditional Formatting Across the Spill Range

Visualise scenario variance across the sensitivity grid using modern spill range conditional formatting:

1. Select the top-left cell of the calculated grid (e.g. B6).
2. Click Home > Conditional Formatting > Color Scales.
3. Select the Green – Yellow – Red scale (where higher Net Present Value renders in dark green and lower valuations render in soft red).
4. To ensure the formatting dynamically expands if the table size changes, enter the spill range operator: =B6# under Applies to in the Conditional Formatting Rules Manager.

Troubleshooting MAKEARRAY Sensitivity Tables and Performance Errors

Consult this diagnostic reference table to debug calculation faults and spill collisions:

Issue / Error Code	Underlying Root Cause	Remediation Procedure
#SPILL!	One or more cells in the anticipated spill trajectory contain existing text, numbers, or hidden formatting.	Clear all data from the cells surrounding the formula. The blue bounding box will indicate the obstructed boundary.
#NUM!	Discount rate equals or is lower than growth rate (d <= g), causing division by zero in the Gordon Growth formula.	Wrap the terminal value calculation in IF(d > g, ..., "N/A - Rate Inversion") to handle impossible financial conditions gracefully.
#CALC!	MAKEARRAY arguments for rows or columns evaluate to zero, negative integers, or exceed Excel memory limits.	Verify that ROWS(row_vars) and COLUMNS(col_vars) point to non-empty vectors.
Noticeable lag when opening on older Excel versions.	Workbook opened in Excel 2016 or 2019 that lacks dynamic array and LAMBDA calculation engines.	Ensure all financial model consumers are licensed on Microsoft 365 Apps for Enterprise or modern Excel for the Web.

Transitioning from legacy What-If Data Tables to modern MAKEARRAY and LAMBDA formulas equips financial analysts with robust, multi-threaded sensitivity modeling capabilities, ensuring rapid scenario testing and professional presentation integrity.