Single value expected, but array delivered

Question

Hi @ all!

This formula should spill all data from January 2025 and expand this array by 3 (3 = WEEKDAY from January 1st, 2025). But it only works if the @ is added:

=LET(x,DATE(2025,1,SEQUENCE(31)),WT,WEEKDAY(INDEX(x,1),2),EXPAND(x,ROWS(x)+**@**WT,1,0))

Obviously the variable WT does not contain a single value (as expected from me) but an array. Furthermore, not a one-dimensional but a two-dimensional array, because this formula (without @) also works:

=LET(x,DATE(2025,1,SEQUENCE(31)),WT,WEEKDAY(INDEX(x,1**,1**),2),EXPAND(x,ROWS(x)+WT,1,0))

If I reduce the formula like this as a test for WT and evaluate it with F9

=LET(x,DATE(2025,1,SEQUENCE(31)),WT,WEEKDAY(INDEX(x,1),2),WT)

an array appears:  ={3}

With INDEX(x,1,1) instead of INDEX(x,1) the result is =3 and not ={3}

Is this a desired behavior?

MG, Boris

Answer 1

Hi,

B2 formula: the @ was born as an implicit intersection operator to perform implicit intersections in dynamic arrays;

If you add the @ symbol to the formula, the formula section to the right of the @ will return the following content:

If the value is a single item, it is returned directly;

If the value is a range, the value in the cell on the same row or column of the formula is returned;

If the value is an array, the value in the upper left corner is returned.

F2 formula: INDEX(X,1,1) >> INDEX(array, row_num, [column_num])

• array    Required. A range of cells or an array constant.
  • If array contains only one row or column, the corresponding row_num or column_num argument is optional.
  • If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
• row_num    Required, unless column_num is present. Selects the row in array from which to return a value. If row_num is omitted, column_num is required.
• column_num    Optional. Selects the column in array from which to return a value. If column_num is omitted, row_num is required.

In the F2 formula, you set the Column value, so it returns the column values as result.

So two formulas that return the array as result but not a single value.

Hope the information is helpful.

---

If the response is helpful, please click "Accept Answer" and upvote it.

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Thank you.

The main thing is (without zero for row or column):

The horizontal result is different than the vertical result.

=INDEX(SEQUENCE(,5),1) = 1

=INDEX(SEQUENCE(5),1) = {1}

But only with arrays. With ranges both results are the same and return a single value:

=INDEX(A:A,1) returns A1 as a single value.

=INDEX(1:1,1) also returns A1 as a single value.

That remains incomprehensible to me.

MG, Boris

Answer 3

Thanks again!

Index has different logic when dealing with horizontal and vertical array constants, but I have not found relevant documentation to explain this.

That`s the point! I didn't find it either. So I accept the behavior without fully understanding it.

Deal done! :-)

MG, Boris