Fix Excel BYROW #VALUE Errors with Spilled XLOOKUP

A user on an Excel Tech Community forum recently asked: “I am using BYROW to iterate through a list of IDs and perform an XLOOKUP for each. My XLOOKUP is supposed to return three columns of data. However, as soon as I wrap it in BYROW, I get a #VALUE! error. If I run the XLOOKUP normally, it spills fine. Why does it break inside the LAMBDA?”

This is a common frustration for users moving into advanced dynamic arrays. The root cause is that Excel currently does not support “Arrays of Arrays.”

The Minimal Working Example

This error typically occurs when you ask BYROW to return more than one value per row, or when you try to use the spill operator (#) on a variable defined within the LAMBDA.

The Problem Formula (Returns #VALUE!):

=BYROW(A2:A10, LAMBDA(row, XLOOKUP(row, E2:E100, F2:G100)))

In this example, XLOOKUP tries to return 2 columns (F and G) for every 1 row, which BYROW cannot process.

The Working Formula (The Fix):

=DROP(REDUCE(0, A2:A10, LAMBDA(acc, val, VSTACK(acc, XLOOKUP(val, E2:E100, F2:G100)))), 1)

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

---

Why the Error Happens

Excel’s Lambda Helper Functions (LHFs) like BYROW, BYCOL, and MAP expect the internal LAMBDA to return a single result (a scalar) for each iteration to neatly pack them into a final array.

Function	Expected Lambda Output	What Happens with Spilled XLOOKUP
BYROW	One value (or one array row)	Fails if XLOOKUP returns multiple columns.
MAP	One value per cell	Fails if the result is an array/range.
REDUCE	An accumulated value	Works because you can manually “stack” arrays.

When XLOOKUP tries to “spill” inside a BYROW, it creates a nested array structure that Excel’s calculation engine cannot currently render.

---

Step-by-Step Breakdown: The REDUCE Workaround

If you need to return multiple columns for every row in a dynamic way, REDUCE is your best tool. It allows you to append new rows to a growing result set.

1. Initialize the Accumulator: Use REDUCE(0, ...) or REDUCE("", ...) to start the formula. This “0” is a placeholder.
2. Define the Array: Pass the range you want to iterate over (e.g., your list of IDs).
3. The LAMBDA Logic: Use VSTACK to “glue” the result of your XLOOKUP to the previous results (acc).
4. Drop the Placeholder: Because we started with a “0”, the first row of your result will be a 0. Wrap the whole thing in DROP(..., 1) to remove that first dummy row.

Final Formula Construction:

=DROP(
    REDUCE(0, A2:A10,
        LAMBDA(acc, val,
            VSTACK(acc, XLOOKUP(val, E:E, F:H, "Not Found"))
        )
    ),
    1
)

---

Common Traps & Troubleshooting

1. Can I use the Spill Operator (#) inside the LAMBDA?

No. If your BYROW variable is r, you cannot write XLOOKUP(r#, ...). Inside the LAMBDA, r represents the value of the specific row being processed, not the entire range. Treat it as a single cell reference.

2. What if I only need one column back?

If your XLOOKUP is only returning one column and you still see #VALUE!, check for data type mismatches or hidden errors within the ReturnRange. BYROW will fail entirely if even one row results in a calculation error that isn’t handled by IFERROR or the if_not_found argument of XLOOKUP.

3. Performance considerations

REDUCE with VSTACK can be slower on very large datasets (e.g., 50,000+ rows) because it rebuilds the array in memory at every step. For massive datasets, it is often more performant to perform a single XLOOKUP on the entire range (if possible) rather than iterating row-by-row: =XLOOKUP(A2:A10, E:E, F:H) Note: This only works if you don’t have complex conditional logic that requires BYROW in the first place.

Related Questions

“Can I use this with filtered rows?” Yes. If you wrap your input range in FILTER, e.g., BYROW(FILTER(A2:A10, B2:B10="Active"), ...), the formula will only iterate through the visible/matching results. The REDUCE method handles this perfectly.

“Is there a way to do this with Power Query instead?” Absolutely. If your logic becomes too complex for REDUCE, a Merge Query in Power Query is significantly more stable. You can perform a Left Outer Join between your main table and your lookup table, which effectively does a “multi-column XLOOKUP” for every row without the risk of nested array errors.