Forum Discussion
Another issue with BYROW
Since you use BYROW() each element of SEQUENCE(2) is passed to lambda as an array, i.e. it will be like
INDEX( {1,2;3,4},{i},)To return entire row in this case you shall use column numbers as array as well
=INDEX({1,2;3,4},{1},{1,2})MAP() returns single value of the array element, thus it works.
With BYROW() - yes, we need to transform an array to single value, as "@i" or INDEX(i,1,1).
Since you use BYROW() each element of SEQUENCE(2) is passed to lambda as an array, i.e. it will be like
INDEX( {1,2;3,4},{i},)To return entire row in this case you shall use column numbers as array as well
=INDEX({1,2;3,4},{1},{1,2})MAP() returns single value of the array element, thus it works.
With BYROW() - yes, we need to transform an array to single value, as "@i" or INDEX(i,1,1).
SergeiBaklan thanks for your response, without being able to debug what is really happening internally it is difficult to understand what is going on.
For example you said that BYROW passes each element as an array and the INDEX function, when rows/columns are indicated as an array, then the other argument should be provided too because using the default values (, or ,0) don't work. Correct? That is why with @-operator works because you cast the array to a scalar value and then default value works on the other argument. Interesting. I didn't know that, because this behavior, when row_num or col_num are arrays, is not explained in INDEX documentation.
Another curiosity about this issue with BYROW, if you enter the following commands: F2 and then F9 on the formula of my question it shows the result will be {3;7}, but then the result is not this one. So the evaluation is not the same as the final result.
Thanks again for your response,
Yes, BYROW() always passes to lambda entire row, i.e. array of elements. Even if that's only one value it is not auto converted into single value.
Yes, how INDEX() works with arrays of rows/columns is not documented directly. Some explanation is in Can Excel's INDEX function return array? - Stack Overflow and in related links, includes trick with N(IF(..)) which JosWoolley mentioned. But that all is for pred-DA Excel, behaviour in modern Excel is bit different. N() in it also returns an array.
I'm not sure how exactly INDEX() works with array/columns elements, at least not to explain in details. And the most interesting question is why F9 evaluates result correctly. Perhaps JoeMcDaid or someone else from the team could give more details.
