Index rows and columns unexpected results

user_12634230 0
Viewed 100

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? enter image description here

excel-formula excel
1 Answers

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, an array constant is returned, and so SEQUENCE(2) returns {1;2}. When used twice, Excel processes {1,1};{2,2}.

You can use SEQUENCE to return a vertical array constant, such as

SEQUENCE(1,2)

which returns {1,2}.

Now it works:

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

Or, using a mix of horizontal and vertical array constants

=INDEX(A1:B3,{1;2},{1,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

user_43619990
Related