Fix Excel LET and LAMBDA VALUE Errors with INDIRECT

A user in an Excel Power User forum recently posted: “I am trying to build a modular dashboard using LET and LAMBDA. I have a cell that defines a sheet name, and I’m using INDIRECT to grab a dynamic array (spilled range) from that sheet. It works in a normal cell, but as soon as I put it inside a LET or a LAMBDA definition, I get a #VALUE! error. How do I get LET to recognize my indirect dynamic range?”

This is a known limitation in how Excel’s calculation engine handles “volatile” references inside the scope of advanced functional programming tools like LAMBDA.

The One-Click Quick Fix

Instead of referencing the spilled range directly inside INDIRECT (e.g., INDIRECT("Sheet1!A1#")), wrap the INDIRECT function in an INDEX or CHOOSECOLS function to force Excel to resolve the reference before the LET or LAMBDA processes it.

Try this pattern: =LET(src, INDEX(INDIRECT("Sheet1!A1#"), 0, 0), SUM(src))

---

Step-by-Step Breakdown

Tested on Excel for Microsoft 365 (Version 2402) as of 2024.

1. The Problem: Why it Breaks

INDIRECT is a volatile function that returns a reference, not necessarily an array of values immediately. When placed inside a LET or LAMBDA, Excel often struggles to resolve the “spill” operator (#) because the calculation sequence for dynamic arrays happens after the reference resolution. This results in the frustrating #VALUE! error.

2. The Solution: Coerce the Array

To fix this, you must “coerce” the reference into a concrete array of values so that the LET variables can hold the data properly.

Function Component	Purpose	Why it helps
INDIRECT("Sheet1!A1#")	Fetches the reference.	The starting point (fails on its own).
INDEX(..., 0, 0)	Selects all rows/columns.	Forces Excel to “materialize” the reference into a data array.
CHOOSECOLS(..., 1)	Selects specific data.	An alternative way to force evaluation if you only need one column.
+0 or --	Mathematical coercion.	Can sometimes force a range to resolve if the data is numeric.

3. Detailed Implementation Example

Imagine you have sheet names in Column A and you want to sum the spilled range starting at A1 on each of those sheets.

The Broken Formula: =MAP(A1:A3, LAMBDA(s, SUM(INDIRECT(s & "!A1#")))) — Returns #VALUE!

The Fixed Formula:

=MAP(A1:A3, LAMBDA(s,
    LET(
        dynamicRange, INDEX(INDIRECT(s & "!A1#"), 0, 0),
        SUM(dynamicRange)
    )
))

---

Common Traps and Considerations

• The “Closed Workbook” Limitation: INDIRECT only works if the source workbook is open. If your LET function references an external file that is closed, you will get a #REF! error regardless of whether you use the INDEX trick.
• Performance Drag: INDIRECT is volatile, meaning it recalculates every time any cell in your workbook changes. If you use this inside a large MAP or REDUCE function over hundreds of rows, your Excel workbook will become sluggish.
• Alternative to INDIRECT: If you only have a few sheets, use SWITCH instead. It is non-volatile and much faster:

  =LET(data, SWITCH(A1, "Sales", SalesSheet!A1#, "Marketing", MktSheet!A1#), SUM(data))

---

Frequently Asked Questions

What if I need to apply this to filtered rows?

If your INDIRECT range is being filtered by a FILTER function inside the LET, ensure the INDEX(INDIRECT(...), 0, 0) happens before the filter. This ensures the filter is acting on a resolved array of data rather than a confused reference.

Can I automate sheet referencing without INDIRECT?

Not easily with standard formulas. To avoid INDIRECT entirely while still being dynamic, you would need to use Power Query to combine your sheets or a small snippet of VBA/Office Scripts. However, for most users, the SWITCH function is the best “clean” alternative if the number of sheets is manageable.

Why does the #VALUE! error only appear sometimes?

This often happens when the source range is empty or contains a single cell. Excel handles single-cell “arrays” differently than multi-cell “spilled ranges.” Wrapping your reference in TOCOL() or INDEX() standardizes the input so the formula remains robust.