Index rows and columns unexpected results

Question

I'm trying to understand the following behaviour:

If I have the following data:

A	B
a	1
b	2
c	3

If I use =INDEX($A$1:$B$3,,) It will correctly show the whole range.

If I use =INDEX($A$1:$B$3,1,) It will correctly show the data for the first row for both columns.

If I use =INDEX($A$1:$B$3,SEQUENCE(2),) I expect it to show the data for the first two rows for both columns. Instead it shows the data of the first two rows, not showing data for the second column.

How come INDEX loses the column reference here?

Answer 1

INDEX reads its parameters as a pair of lists.

For example, using array constants, you can type:

=INDEX(A1:B3,{1,3},{1,2})

which gives:

a    3

because Excel reads this as {1,1}, {3,2}.

With SEQUENCE, either a horizontal or vertical array constant is returned, and so SEQUENCE(2) returns {1;2}, a vertical array (indicated by the semi-colon)

and

SEQUENCE(1,2)

which returns {1,2}.

Now it works:

=INDEX(A1:B3,SEQUENCE(2),SEQUENCE(1,2))

Or, using array constants

=INDEX(A1:B3,{1;2},{1,2})

This produces output from cells

(1,1),(1,2) ; (2,1),(2,2)

Ref:

https://support.microsoft.com/en-us/office/guidelines-and-examples-of-array-formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

Create one and two-dimensional array constants