Question Details

No question body available.

Tags

excel excel-formula

Answers (7)

Accepted Answer Available
Accepted Answer
April 6, 2026 Score: 6 Rep: 1,592 Quality: Expert Completeness: 80%

Sort by column via array reshaping (broadcast and flatten):

=WRAPCOLS(
    SORTBY(
        TOCOL(IF({1}, itemsrows, datavalues)),
        TOCOL(IF({1}, SEQUENCE(, COLUMNS(datavalues)), datavalues)), 1,
        TOCOL(datavalues), 1
    ),
    ROWS(datavalues)
)
  • IF is used to broadcast (repeat) the itemsrows vector across the datavalues array by passing an array object, {1}, to its logicaltest argument. Since 1 is interpreted as TRUE, only the valueiftrue argument is returned.
  • The same process is also applied to a horizontal vector of column index numbers. (NOTE: SEQUENCE(, COLUMNS(datavalues)) was used as a generalized method, but could be replaced with itemscols if it already contains sequential column index numbers, e.g. {1,2,3,4,...}).
  • TOCOL flattens each array by sending their values to a single column (vertical vector).
  • SORTBY then sorts the resulting vector of itemsrows values, first by column index number in ascending order, then by datavalues in ascending order.
  • Lastly, WRAPCOLS restores the dimensions of the original table/array.

Sort by column via recursive bisection (a.k.a. divide and conquer):

=LET(
    fn, LAMBDA(me,arr,
        IF(
            COLUMNS(arr) = 1,
            SORTBY(itemsrows, arr),
            HSTACK(me(me, TAKE(arr, , COLUMNS(arr) / 2)), me(me, DROP(arr, , COLUMNS(arr) / 2)))
        )
    ),
    fn(fn, datavalues)
)

This method creates a binary tree of functions by recursively dividing the datavalues array in half until each leaf node is reached, thus delaying the evaluation of SORTBY(items_rows, arr) until only 1 column remains, and effectively bypassing the operand stack limit that would otherwise be imposed on standard Lambda recursion.

To make a function recursive within the scope of a LET statement, a fixed-point combinator (me) is needed to pass the function to itself after the function name has been defined.

In simpler terms, me is used as a function placeholder, because at this point fn does not exist (it's still being defined), so it cannot yet call itself.

After fn is defined, me becomes fn via fn(fn,...). It's a form of Lambda injection, where the same function is being applied again to all subsequent iterations.

April 2, 2026 Score: 3 Rep: 12,293 Quality: Medium Completeness: 50%

Edit: At first I misinterpret the question.

=DROP(REDUCE(0,B2:E2,LAMBDA(a,b,HSTACK(a,SORTBY(A2:A5,B5:b E5:b)))),,1)

Or more dynamic:

=LET(data,B2:E5,
     items,A2:A5,
DROP(REDUCE(0,TAKE(data,1),LAMBDA(a,b,HSTACK(a,SORTBY(items,items:b TAKE(data,,-1):b)))),,1))

Old answer:

If you plan to use MAP, you could make use of IFNA to make the item values in the headers spill the size of the values:

=LET(x,B1:E1,y,A2:A5,MAP(IFNA(y,x),IFN

April 2, 2026 Score: 3 Rep: 57,057 Quality: Medium Completeness: 70%

Sort Column by Column(s)

Best

=LET(r,A2:A5,v,B2:E5,
    DROP(REDUCE("",SEQUENCE(COLUMNS(v)),LAMBDA(a,i,
        HSTACK(a,SORTBY(r,INDEX(v,,i))))),,1))

SORTBY(r,INDEX(v,,i)) is the same as DROP(SORT(HSTACK(INDEX(v,,i),r)),,1)!!!

Initial Answer

=LET(r,A2:A5,v,B2:E5,
    DROP(REDUCE("",SEQUENCE(COLUMNS(v)),LAMBDA(a,i,
        HSTACK(a,DROP(SORT(HSTACK(INDEX(v,,i),r)),,1)))),,1))

Screenshot

G2:Q5 represents SORT(HSTACK(INDEX(v,,i),r)) on each iteration.

April 2, 2026 Score: 3 Rep: 705 Quality: Medium Completeness: 100%

When in doubt, LET it out.

  1. Assign variables to the data that will be repeated, which in this case will be the entire reference and the number columns of the reference. "s" represents the sequential number of columns in the reference {1,2,3,4}. "r" represents the sequential number of rows in the reference {1,2,3,4}.

  2. Then use REDUCE's ability to stack data horizontally with the HSTACK function.

  3. the LAMBDA's variables represent the first and second arguments of the REDUCE function. I used "a" for the first b, and "v" for each value of SEQUENCE(c) or "s".

  4. Add CHOOSECOLS function inside of HSTACK second argument to select each column of reference variable.

  5. Use the SORTBY function to sort {1,2,3,4} aka "r" or sequence(rows(b)) by each column of the reference (CHOOSECOLS).

  6. Finally drop the first 4 columns - the first iteration of variable b "REDUCE(b,)"

=LET(
  b, B2:E5,
  c, COLUMNS(b),
  s, SEQUENCE(c),
  r, SEQUENCE(ROWS(b)),
  DROP(REDUCE(b,s,LAMBDA(a,v, HSTACK(a, SORTBY(r,CHOOSECOLS(a,v))))),,c)
)

enter image description here

April 2, 2026 Score: 2 Rep: 35,468 Quality: Medium Completeness: 60%

Rather than using map, try Makearray or Reduce

=LET(data,B2:E5,size,COLUMNS(data),MAKEARRAY(size,size,LAMBDA(r,c,INDEX(SORTBY(A2:A5,INDEX(data,0,c)),r))))

or

=LET(data,B2:E5,DROP(REDUCE("",SEQUENCE(COLUMNS(data)),LAMBDA(a,c,HSTACK(a,SORTBY(A2:A5,INDEX(data,0,c))))),,1))

Of course it's possible to use Map, but not really a good idea

=LET(data,B2:E5,size,COLUMNS(data),MAP(SEQUENCE(size,size,0),LAMBDA(a,LET(r,QUOTIENT(a,size)+1,c,MOD(a,size)+1,INDEX(SORTBY(A2:A5,INDEX(data,0,c)),r)))))
1 1 3 3
2 3 4 2
3 2 2 1
4 4 1 4

We established that ranking doesn't generally give the right answer.

If you did want to persevere down this route, you would have to look up the current row number in the list of ranks and find the corresponding item number

=LET(data,B2:E5,size,COLUMNS(data),MAKEARRAY(size,size,LAMBDA(r,c,XLOOKUP(r,RANK(INDEX(data,,c),INDEX(data,,c),1),A2:A5))))

or if ties were possible

  =LET(data,B2:E5,size,COLUMNS(data),MAKEARRAY(size,size,LAMBDA(r,c,XLOOKUP(r,RANK(INDEX(data,,c),INDEX(data,,c),1),A2:A5,,-1))))

If there were ties, this would give different results from other answers. Suppose you had this data

Items 1 2 3 4
1 0.11 0.21 0.34 0.41
2 0.11 0.23 0.31 0.41
3 0.13 0.23 0.31 0.41
4 0.14 0.23 0.32 0.41

Sorting methods would give tied items in their original order
1 1 2 1
2 2 3 2
3 3 4 3
4 4 1 4

My method would give tied items as the first matching item
1 1 2 1
1 2 2 1
3 2 4 1
4 2 1 1
April 2, 2026 Score: 2 Rep: 7,796 Quality: Low Completeness: 70%

The below formula is in cell H2 and spill the result.
The formula add a column with a sequenced number of rows in the table, then SORT, returns the sorted rows numbers and then create the table with TEXTJOIN and TEXTSPLIT functions.
All ranges in the formula are the table data (in sample data B2:E6) without the row/column titles.

=WRAPCOLS(TEXTSPLIT(TEXTJOIN("|",FALSE,BYCOL(B2:E6,LAMBDA(x,TEXTJOIN("|",FALSE,TAKE(SORT(HSTACK(x,SEQUENCE(ROWS(B2:E6))),1),,-1))))),"|"),ROWS(B2:E6))

enter image description here

EDIT
Thanks for @rotabor observation the edited formula works with tables of different row and column count.

April 2, 2026 Score: 0 Rep: 21 Quality: Low Completeness: 40%

enter image description here

Apply below formula in cell G1:

=LET(
 srt, SORT(TOCOL(B2:E6, 1)),
ifna, IFNA(--TRANSPOSE(TEXTSPLIT(TEXTJOIN(";",, DROP(GROUPBY(INT(srt * 10), srt, ARRAYTOTEXT,, 0),, 1)), ", ", ";")), ""),
      VSTACK(HSTACK("Items", SEQUENCE(, COLUMNS(ifna))), HSTACK(SEQUENCE(ROWS(ifna)), ifna))
)