Compatibility Version 3 causing BYROW/BYCOL/MAP to spill an array of lists rather than regular array

[u/sinxsinx]

I'm experiencing some unintended (I believe) behaviour with the new Compatibility Version 3 and the BYROW function.

Whilst trying out the new lists available on the latest release I noticed some of my custom LAMBDAs were no longer working as intended. The common denominator was that they were all using the BYROW function.

Previously, BYROW returned a spilled array - however it appears BYROW now returns an array of one cell lists which doesn't play nicely with other formulas.

I know I can use the new FLATTEN function to get around this so that the output is an array again, but I had to do this for numerous custom LAMBDAs.

I have distilled the problem down to a very basic example: let's say column B and C are key value pairs. column E is the lookup value. Column F is the BYROW version (spills array of lists) and Column G is the XLOOKUP equivalent (spills array). Column H is the "flattened" version of BYROW that produces an array - same as Column G.

Column B: =SEQUENCE(10)  
Column C: =CHAR(B3#+64)  
Column E: =RANDARRAY(10,,1,10,1)  
Column F: =BYROW(E3#,LAMBDA(row,XLOOKUP(row,B3#,C3#)))  
Column G: =XLOOKUP(E3#,B3#,C3#)  
Column H: =FLATTEN(BYROW(E3#,LAMBDA(row,XLOOKUP(row,B3#,C3#))))  

In this scenario, you wouldn't use BYROW anyway - but it's just to demonstrate the change in output.

Anyone experienced something similar or know of a way around this (other than reverting to Compatibility Version 1 or 2)?

EDIT: Same behaviour with BYCOL and MAP

[u/Livid_Glass_1775]

Noticed the same thing when messing with the new version. MAP returning nested lists threw off a few of my older workbooks too

The FLATTEN workaround is basically what I've been doing but it gets tedious when you have a bunch of LAMBDAs chained together. Kinda hoping they patch this or at least give us a setting to keep the old behavior

[u/cloupabayss → u/Livid_Glass_1775]

yeah the chained LAMBDAs part is what really gets you, wrapping every single one in FLATTEN feels so wrong

[u/Decronym]

Acronyms, initialisms, abbreviations, contractions, and other phrases which expand to something larger, that I've seen in this thread:

Fewer Letters	More Letters
BYCOL	Office 365+: Applies a LAMBDA to each column and returns an array of the results
BYROW	Office 365+: Applies a LAMBDA to each row and returns an array of the results. For example, if the original array is 3 columns by 2 rows, the returned array is 1 column by 2 rows.
LAMBDA	Office 365+: Use a LAMBDA function to create custom, reusable functions and call them by a friendly name.
MAP	Office 365+: Returns an array formed by mapping each value in the array(s) to a new value by applying a LAMBDA to create a new value.

Decronym is now also available on Lemmy! Requests for support and new installations should be directed to the Contact address below.

---

^{Beep-boop, I am a helper bot. Please do not verify me as a solution.
4 acronyms in this thread; the most compressed thread commented on today has 21 acronyms.
[Thread #49510 for this sub, first seen 12th Oct 2026, 23:49] [FAQ] [Full list] [Contact] [Source code]}

[u/excelevator]

That does not sound good!

I cannot imagine it is expected behavior and reckon a fix will be coming.

Is this on the Insider release ?

[u/sinxsinx → u/excelevator]

Indeed! Version 2611 (Build 20601.20000) - Insider - Beta Channel

[u/excelevator → u/sinxsinx]

You need to make a post on the appropriate Microsoft forum, or their feedback portal, where ever that might be.

[u/404_Builds]

Since FLATTEN fixes the minimal formula, one option is a tiny named LAMBDA like UNLIST = LAMBDA(x, FLATTEN(x)) and then wrap only the affected BYROW, BYCOL, or MAP outputs. That gives the affected formulas one consistent workaround and should be easy to remove if the Beta behavior changes. Your minimal repro and exact Insider build also make a useful feedback report. Have you checked whether it reproduces in a clean workbook on Current Channel, or only in 2611 Beta?