How to Fix Excel Nested MAP Functions That Return #VALUE! Errors
Question details
The user wants to nest one Excel MAP function inside another to produce a two-dimensional calculation result without receiving #VALUE! errors.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- A formula uses an inner MAP that should return a multi-cell array to an outer MAP, but Excel cannot pass nested arrays through MAP.
- Observed behavior
- Excel returns #VALUE! because MAP does not support returning nested arrays; SUM-based scalar results may look correct but do not fix the array limitation.
Before you edit the formula, verify that your Excel version supports LAMBDA, MAP, MAKEARRAY, REDUCE, VSTACK, and BYCOL. If MAP or SCAN has started returning inconsistent results, save your workbook and restart Excel first.
Return a Two-Dimensional Result with MAKEARRAY Instead of Nesting MAP
Use this when the outer MAP must consume the inner MAP result; MAKEARRAY creates the 2D array directly and avoids the nested-array restriction.
MAP can transform each row or column, but it cannot return a nested array that another MAP then processes. MAKEARRAY is designed to produce a grid by calling a calculation for each row and column index.
Select the formula cell and open the formula bar, or click Formulas > Name Manager to create a reusable LAMBDA.
Use =MAKEARRAY(row_count, column_count, LAMBDA(r,c, calculation)) where calculation uses INDEX to read the input ranges and returns a scalar for each cell.
Make sure the final LAMBDA in MAKEARRAY returns one value per r,c pair, not an array, and press Enter to spill the 2D result.
Stack Inputs and Use BYCOL or REDUCE with VSTACK
Use this when the calculation can be rearranged as column-by-column processing or when you need to accumulate rows without nesting MAP.
Restart Excel and Test the Dynamic Array Functions Separately
Use this if MAP, SCAN, or BYCOL suddenly returns #VALUE! even when copied directly from documentation.
Use WPS Spreadsheets as a Free Microsoft Excel Alternative
When Excel nested MAP errors slow you down, WPS Office offers a free, lightweight alternative that opens and saves Excel workbooks, keeps a familiar spreadsheet interface, and makes migration easy without sacrificing compatibility.
- 1. Download WPS Office: Go to the official WPS Office download page and click Download WPS Office Free to get the desktop installer.
- 2. Install and open WPS Spreadsheets: Run the installer, launch WPS Spreadsheets from the WPS Office home screen, and open your existing .xlsx workbook.
- 3. Review and continue editing: Check the formula cells and use WPS Spreadsheets tools or helper ranges to complete your spreadsheet work without the Excel session instability.
Frequently Asked Questions
Why does nesting MAP inside MAP return #VALUE! in Excel?
Excel's MAP function cannot return a nested array to an outer MAP. Even if you wrap the inner MAP in SUM or another scalar function, the outer MAP still cannot consume a multi-cell inner array; use MAKEARRAY, REDUCE with VSTACK, or BYCOL to produce the two-dimensional result instead.
Can I use VSTACK inside BYCOL as a nested MAP workaround?
Yes. Stack the input rows with VSTACK and then process each column with BYCOL. Inside the BYCOL LAMBDA, use INDEX or LET to unpack the stacked values, so the function returns one scalar per column rather than a nested array.
Why do MAP or SCAN formulas sometimes work and then fail with #VALUE!?
This can happen when the Excel session becomes unstable. Save your workbook, close Excel completely, reopen it, and test MAP or SCAN in a blank workbook before re-entering the full formula.
Does WPS Office support Excel files with LAMBDA and dynamic arrays?
WPS Spreadsheets opens and saves .xlsx files and supports common spreadsheet features. For complex LAMBDA-based workbooks, keep the original file intact and verify formulas after opening in WPS, because dynamic array behavior may differ between spreadsheet engines.
