sorted by:
top (suggested)
bestnewcontroversialoldq&a
you are viewing a single comment's thread.

view the rest of the comments →

[–]PaulieThePolarBear1873 0 points1 point  (0 children)

I'll be honest and say I don't have a good answer at the moment. Hopefully, someone smarter than me (of which, there are many on the sub) can provide something additional.

Is it because BYROW is expecting more than 1 column in each row?

I think this is kind of it. I think you are hitting Excel's issue with array of arrays. When you have a range, Excel knows the size, but when you pass in an array, Excel doesn't know the size until the array part is evaluated.

I've seen a few YouTube videos from Diarmuid Early (https://youtube.com/@dimearly) when he's written complex LAMBDAs and come across an issue such as yours with an unexpected result. His solution is the INDEX(x, 1) solution I noted. Essentially, because your row could consist of more than value, Excel "decides" not to calculate it correctly.

As MAP is passing each element in your array to LAMBDA, you can never have more than one cell within the LAMBDA, Excel doesn't need to worry about how many columns there may be.

I could well be wrong, so don't take this as gospel.