excel formula index() not returning row with dynamic row number -EXCEL 2013

Question

I need to sort an excel table into some other range using formula, like this:

Oritinal table:
    A      B      C      D              
1   name val1   val2    val3                  name  val1   val2    val3
2   NN   5.70   0.50    1.20                    LD  2.30   0.90    2.40
3   LD   2.30   0.90    2.40   sort by val1=>   PL  4.60   0.80    3.30
4   PL   4.60   0.80    3.30                    NN  5.70   0.50    1.20

step1: Using array formula {=INDEX($A$2:$D$4,MATCH(SMALL($B$2:$B$4,ROW()-ROW(A$7)+1),$B$2:$B$4,0),0)}, I get the following result:

7 LD    LD    LD    LD
8 PL    PL    PL    PL
9 NN    NN    NN    NN

step2: Then I tested the index formula as {=INDEX($A$2:$D$4,2,0)}, here I specified a static row number, the index function returned a full row as expected:

7 LD    2.3    0.9    2.4
8 LD    2.3    0.9    2.4
9 LD    2.3    0.9    2.4

step3: Then another test with dynamic row numbers as {=INDEX($A$2:$D$4,ROW() - 6,0)}, now the function only return the first column as step1:

7 NN    NN    NN    NN
8 LD    LD    LD    LD
9 PL    PL    PL    PL

Why index() returns just the first column with dynamic row numbers?

How can I sort the table with just formula (no vba, no GUI operation)?

Use your first formula but not as an array. Use =INDEX($A$2:$D$4;MATCH(SMALL($B$2:$B$4;ROW()-ROW(D$7)+1);$B$2:$B$4;0);0) as your formula. Copy your formula to you targeted range and you'll get the result you wanted. — ctumturk
Thank you @ctumturk, it worked. I expected the array function to work and then transpose the result. And I still like to know why can't I use dynamic row numbers in index(). — chenxin
with normal formula, you find the exact value and return that value. But with array formula you find the array of that value and you return the 0th column of that array. if you modify your first formula like this{=INDEX($A$2:$D$4;MATCH(SMALL($B$2:$B$4;ROW()-ROW(A$7)+1);$B$2:$B$4;0);COLUMN(A$7))} it should be enough. — ctumturk

p._phidot_ p._phidot_ · Accepted Answer · 2018-07-22T19:22:57

Why index() returns just the first column with dynamic row numbers?

it should be =INDEX($A$2:$D$4,0,Column())

How can I sort the table with just formula (no vba, no GUI operation)?

you can. you can use small() or large() function to get the val1, then use index/match or vlookup to fill in other values based on val1 you got. (pls comment if you/others get stuck).

+----[edit]----+

Implimentation :

make

G2 = 1
G3 = 2
G4 = 3

and

H2       =INDEX(A:A,MATCH($I2,$B:$B,0))
I2       =SMALL(B:B,G2)
J2       =INDEX(C:C,MATCH($I2,$B:$B,0))
K2       =INDEX(D:D,MATCH($I2,$B:$B,0))

and drag until K4. Done.

Hope that helps. (:

excel formula index() not returning row with dynamic row number -EXCEL 2013

1 Answers