CODEWITHSUNDEEP
excelexcel-formula
chenxin
chenxin

Reputation: 423

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

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)?

Upvotes: 1

Views: 876

Answers (1)

p._phidot_
p._phidot_

Reputation: 1925

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. (:

Upvotes: 0

Related Questions