Fix Excel LAMBDA #VALUE Error with Nested MAP and Recursion

A user on the Microsoft Tech Community recently asked: “I am trying to create a recursive LAMBDA to traverse a bill of materials. It works fine when I pass a fixed range, but as soon as I pass a dynamic array reference (like A2#) into a MAP function nested inside the recursion, I get a #VALUE! error. Is there a limitation on nested dynamic arrays?”

This is a common “wall” developers hit in Excel. The issue stems from Excel’s inability to handle “Arrays of Arrays.” When you pass a dynamic array reference into a MAP function that is already being called recursively, Excel often fails to resolve the dimensions correctly, leading to the #VALUE! error.

The most robust way to solve this is to stop MAP from trying to iterate over the dynamic reference directly and instead use INDEX to “peek” into the array by row number.

The Minimal Working Example

In this example, we will build a function called SUM_LEVELS that recursively sums values.

Tested on: Excel for Microsoft 365 (Version 2408) as of late 2024.

The “Broken” Logic

Many users try this: =LAMBDA(data_ref, MAP(data_ref, LAMBDA(row, ... recursive call ...))) When data_ref is A2#, the recursion often collapses because MAP loses the context of the array’s boundaries during the second cycle.

The Fix: The INDEX-SEQUENCE Pattern

Instead of mapping the array, map a sequence of numbers representing the rows of that array.

/* Define this in the Name Manager as: RECURSE_SUM */
=LAMBDA(qty_array, current_index,
    IF(current_index > ROWS(qty_array),
        0,
        INDEX(qty_array, current_index) + RECURSE_SUM(qty_array, current_index + 1)
    )
)

To call this on a dynamic array starting at A2#, you would use: =RECURSE_SUM(A2#, 1)

---

Why This Works

Excel’s Lambda Helper Functions (LHFs) like MAP, SCAN, and REDUCE are optimized for flat operations. When you nest them inside a recursive function, the “pointer” to the dynamic array (#) can become ambiguous to the calculation engine.

By passing the entire array and a separate index counter:

1. Memory Stability: Excel maintains one reference to the array in memory rather than trying to “slice” it into smaller sub-arrays during every recursive step.
2. Avoids “Array of Arrays”: Excel’s engine cannot currently return an array where each element is itself an array. Using INDEX extracts a single scalar value, which Excel handles perfectly.

Parameter Breakdown

Argument	Type	Description
qty_array	Reference	The dynamic array reference (e.g., A2# or FILTER(...)).
current_index	Integer	The starting row (usually 1). Increments by 1 each loop.
ROWS(qty_array)	Function	The “Exit Condition” that prevents infinite recursion.

---

Adapting to Your Own Data

If you need to perform a more complex calculation (like a hierarchical lookup) instead of a simple sum, follow these steps:

1. Open the Name Manager: Go to Formulas > Name Manager > New.
2. Name your function: Give it a clear name like PROCESS_TREE.
3. Use the Sequence Pattern:
   • Instead of MAP(range, ...), use MAP(SEQUENCE(ROWS(range)), LAMBDA(r, ...)).
   • Inside your inner logic, refer to your data using INDEX(range, r, column_number).
4. Handle the Base Case: Always ensure your IF statement has a clear exit point (e.g., when the counter exceeds the total row count) to avoid the #NUM! error.

---

Frequently Asked Questions

What if I need to apply this to filtered rows?

Dynamic array references (#) automatically respect the results of a FILTER function. If your source is =FILTER(A1:B10, B1:B10>5), passing that spill range into the INDEX pattern described above will work seamlessly without any extra configuration.

Is there a limit to how deep the recursion can go?

Yes. Excel has a recursion limit (typically around 1,024 levels). If you are processing a massive hierarchy (like a global supply chain BOM), a recursive LAMBDA might trigger a #CALC! error. In those specific cases, using Power Query with a recursive “Grouped Row” step is more performant.

Can I automate this with Office Scripts?

If the LAMBDA logic becomes too complex to debug, you can write an Office Script (available in Excel for Web and Business versions). Office Scripts use TypeScript, which handles recursion much more naturally than the Excel formula engine and won’t throw #VALUE! errors due to array referencing issues.