Fix Excel #SPILL Errors in LAMBDA with Dynamic Table Columns

A user in a Microsoft Excel Tech Community thread recently asked: “I am trying to create a custom function using LAMBDA that performs a calculation on a specific column in my Table. I want to pass the column name as a text string, so I used INDIRECT("Table1[" & col_name & "]"). It works fine on its own, but as soon as I put it inside a MAP or BYROW function to process multiple rows, I get a #SPILL! error. How do I reference table columns dynamically without breaking the calculation?”

The issue arises because INDIRECT returns a range or array. When nested inside a Dynamic Array function like MAP, Excel often struggles to resolve the “array of arrays” structure, resulting in a #SPILL! or #CALC! error.

Comparing Methods for Dynamic Column Referencing

When working within the LAMBDA environment, how you reference your data determines if the formula will scale or fail.

Method	Syntax Style	Volatility	Dynamic Array Friendly?
INDIRECT	INDIRECT("Table[Col]")	High (Slows workbook)	No (Often causes #SPILL)
INDEX/MATCH	INDEX(Table, 0, MATCH(...))	Low	Yes
CHOOSECOLS	CHOOSECOLS(Table, XMATCH(...))	Low	Recommended

---

The Recommended Solution: CHOOSECOLS + XMATCH

Instead of using INDIRECT, which forces Excel to re-evaluate the entire sheet on every change, use CHOOSECOLS. This function is specifically designed to work with dynamic arrays.

Tested on: Excel for Microsoft 365 and Excel Online (as of 2024).

Step 1: Set up your Table

Ensure your data is formatted as an official Excel Table (Insert > Table). For this example, let’s assume your table is named SalesData and has columns named Revenue and Region.

Step 2: Create the LAMBDA logic

If you want to create a named function that calculates a “Tax” (10%) on any column you name, the logic should look like this:

=LAMBDA(col_name,
    LET(
        col_index, XMATCH(col_name, SalesData[#Headers]),
        target_col, CHOOSECOLS(SalesData, col_index),
        target_col * 0.1
    )
)

Step 3: Define the Name

1. Copy the formula above (without the =).
2. Go to Formulas > Name Manager.
3. Click New.
4. Name it CALC_TAX.
5. Paste the formula into the Refers to box.

Step 4: Use it in your sheet

Now, you can call this function using a string from another cell: =CALC_TAX("Revenue")

This will return the entire calculated column as a single dynamic array without any spill errors.

---

Why this fixes the #SPILL error

Excel’s dynamic array engine cannot handle “Nested Arrays.” If you use MAP(Range, LAMBDA(x, ...)) and the formula inside the LAMBDA returns an array (which INDIRECT often does), Excel doesn’t know where to put those results because one cell cannot hold multiple values from the MAP iteration.

By using CHOOSECOLS and XMATCH inside a LET function, you are:

1. Identifying the column index number first (a single value).
2. Extracting the column as a single reference.
3. Allowing Excel’s calculation engine to treat the output as one continuous spill range rather than a nested calculation.

---

Common Traps & Troubleshooting

• Header Mismatches: XMATCH is exact by default. If your col_name string has a trailing space (e.g., “Revenue “) but the Table header is “Revenue”, the formula will return #N/A. Use TRIM(col_name) inside your XMATCH to be safe.
• The Entire Table Reference: Ensure your CHOOSECOLS points to the data area of the table (SalesData), while your XMATCH points to the headers (SalesData[#Headers]). If the ranges are different widths, the index will be wrong.
• Performance Issues: If you have a table with 100,000+ rows, avoid putting this logic inside a ROW-BY-ROW function like MAP. Instead, pass the whole column to the LAMBDA at once, as shown in the example above.

Frequently Asked Questions

What if I need to reference a different table entirely? You can add a second argument to your LAMBDA to accept the table range: =LAMBDA(target_table, col_name, ...) However, you cannot pass the name of the table as a string without using INDIRECT. To keep it stable, pass the actual table range (e.g., SalesData) as the argument.

Can I use this for filtered rows only? No, CHOOSECOLS and INDEX will return the entire column including hidden rows. If you need to perform calculations only on visible rows, you would need to wrap the target_col in a FILTER function using SUBTOTAL as a helper column.

Does this work in Excel 2021? CHOOSECOLS is exclusive to Microsoft 365. If you are on Excel 2021, use the INDEX method: INDEX(SalesData, 0, MATCH(col_name, SalesData[#Headers], 0))