Search code examples
sortinggoogle-sheetssplitvlookupflatten

Sorting/ordering values from smallest to biggest in an array

I have a formula like this : =ArrayFormula(sort(INDEX($B$1:$B$10,MATCH(E1,$A$1:$A$10,0))))

in columns A:B:

    a   1
    b   2
    c   3
    d   4
    e   5
    f   6
    g   7
    h   8
    i   9
    j   10

and

 the data to convert in E:H
a   c   f   e
f   a   c   b
b   a   c   d

I get the following results using the above formula

in columns L:O:
1   3   6   5
6   1   3   2
2   1   3   4

My desired output is like this:

1   3   5   6
1   2   3   6
1   2   3   4

I'd like to arrange the numbers from smallest to biggest in value. I can do this with additional helper cells. but if possible i'd like to get the same result without any additional cells. can i get a little help please? thanks.

Solution

  • try:

    =INDEX(TRIM(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(E1:H3&"×​"&ROW(E1:H3)), "​"), 
 "select max(Col1) group by Col1 pivot Col2"), "offset 1", 0),,9^9)), "×")))

    2

    or if you want numbers:

    =INDEX(IFNA(VLOOKUP(TRIM(SPLIT(FLATTEN(QUERY(QUERY(QUERY(SPLIT(FLATTEN(E1:H3&"×​"&ROW(E1:H3)), "​"), 
 "select max(Col1) group by Col1 pivot Col2"), "offset 1", 0),,9^9)), "×")), A:B, 2, 0)))

    1