SORTBY

What it does

SORTBY(array, by_array1, [sort_order1], ...)

SORTBY reorders an array using keys held outside it.

Given data A and key vectors K₁, K₂, … all of the same length as A's sorted axis, and a direction d₁, d₂, … for each, SORTBY computes the permutation π that orders the tuple sequence (K₁[i], K₂[i], …) lexicographically — K₁ first, K₂ breaking K₁'s ties, and so on — each component ordered in its own direction. It then applies π to A.

Two properties distinguish it from SORT:

1. The keys need not be in the data. You can sort a visible table by a hidden column, by a computed expression (SORTBY(names, LEN(names))), or by a lookup result. Nothing of the key appears in the output.
2. It is multi-level. SORT sorts by one key. SORTBY composes an arbitrary number of keys with independent directions, which is what "sort by region ascending, then revenue descending" requires.

As with SORT, the result is a permutation of the same shape: nothing is deduplicated, nothing is filtered, and every element of A appears exactly once.

Arguments

Microsoft documents SORTBY(array, by_array1, [sort_order1], [by_array2, sort_order2], …).

The documented shape rules are strict and worth stating exactly, because they are the usual source of #VALUE!:

• each by_array must be one row high or one column wide — a key vector, never a rectangle;
• all the arguments must be the same size as each other, and array's sorted dimension must match the key vectors' length.

The argument positions most often misread are the pairs themselves. Because sort_order is optional within each pair, the arguments after the second are position-sensitive in a way that is easy to get wrong: SORTBY(A, K1, K2) does not mean "two keys" — it means "key K1, sort order K2", and unless K2 happens to be 1 or -1 that is a #VALUE!. Two keys require the explicit order for the first: SORTBY(A, K1, 1, K2, 1).

Try it

Quirks

Result and edge cases

The return kind is an array of the same shape as array, spilled.

• Orientation. Whether SORTBY reorders rows or columns follows from the orientation of the key vectors — a column key sorts rows, a row key sorts columns. There is no by_col flag; the shape carries the intent.
• Ties beyond the last key. When every supplied key ties, the resulting order is whatever the underlying sort does with equal elements. Stability is not documented, and the Handbook has not pinned it.
• Mixed value kinds in a key vector require the same cross-kind total order that SORT needs, and it is equally unverified here.
• Text keys are collated, and collation is locale-dependent.
• Dynamic-array publication (#SPILL! when the result cannot be placed) is host-side adaptation, described in the call pipeline, not a SORTBY outcome.

Errors

Documented by Microsoft:

#SPILL! arises, as for every dynamic-array function, when the result has nowhere to land. Mismatched sizes between array and a by_array, or a rectangular by_array, violate the documented shape rules; the documentation states the requirement without naming the error, and the Handbook has not verified which error results.

How to implement

Notes for implementers

• The multi-key comparison is lexicographic with per-component direction. Implementing it as repeated single-key sorts only works if the underlying sort is stable and the keys are applied in reverse order; if stability is undecided, that trick is unsound.
• The pairing structure means argument parsing is positional and irregular: an odd trailing argument is a key, an even one is a direction. Validate sort_order values eagerly — Microsoft documents #VALUE! for anything other than 1 or -1, so silently accepting 0 or 2 is a divergence.
• Shape validation belongs before sorting: rectangular key arrays and length mismatches are documented as invalid, not as something to broadcast.
• The omitted-slot default path is a recorded defect class in this family.
• The cross-kind comparison order and the collation used for text should be the same ones SORT uses; two orderings in one engine is a bug waiting for a witness.

Rust code

4 sortby_fn "SORTBY" (2, 255) [R "array", R "by_array1", R "sort_order1"] |s, a, sv| {
    sortby(s, a, sv)
}

/// SORTBY: by each by_array, a row or column the length of the array's rows (or of its columns,
/// when the array is one row and a key is a row), with its order.
fn sortby(site: &Site<'_>, a: &[CalcValue], sv: &mut EvalServices<'_>) -> Step<CalcValue> {
    let args = prepared(site, a, sv)?;
    // An argument that is one error value is the result (Excel 16.0.20430, 2026-10-25, dc-6au:
    // `=SORTBY(1,#REF!)` #REF!, `=SORTBY(1,#DIV/0!)` #DIV/0!, `=SORTBY({3;1;2},{3;1;2},#REF!)`
    // and `=SORTBY(1,1,#REF!)` #REF!).
    for argument in &args {
        if let CoreRef::Error(code) = argument.core_ref() {
            return fail(code);
        }
    }
    let array = as_array(&args[0]);
    let ArrayShape { rows, cols } = array.shape();
    let mut keys: Vec<(Vec<CalcValue>, bool)> = Vec::new();
    let mut by_col = None;
    let mut at = 1;
    while at < args.len() {
        let key = as_array(&args[at]);
        at += 1;
        let down = match args.get(at) {
            Some(value) if value.as_array().is_none() => {
                at += 1;
                descending(site, Some(value))?
            }
            _ => false,
        };
        let shape = key.shape();
        let (values, columns) = if shape.cols == 1 && shape.rows == rows {
            (
                (0..rows)
                    .map(|r| key.get(r, 0).cloned().unwrap_or_else(CalcValue::empty))
                    .collect(),
                false,
            )
        } else if shape.rows == 1 && shape.cols == cols {
            (
                (0..cols)
                    .map(|c| key.get(0, c).cloned().unwrap_or_else(CalcValue::empty))
                    .collect(),
                true,
            )
        } else {
            return fail(WorksheetErrorCode::Value);
        };
        if *by_col.get_or_insert(columns) != columns {
            return fail(WorksheetErrorCode::Value);
        }
        keys.push((values, down));
    }
    let by_col = by_col.unwrap_or(false);
    let len = if by_col { cols } else { rows };
    let order = sorted_indices(site, len, &keys)?;
    reordered(&array, &order, by_col)
}

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• SORT is the single-key, internal-key sibling. SORT(A, k, d) is SORTBY(A, INDEX(A, 0, k), d) in spirit; SORTBY is the more general of the two and is the one to reach for whenever the key is computed or the sort is multi-level.
• UNIQUE deduplicates; SORTBY never does.
• FILTER selects; SORTBY reorders. SORTBY(FILTER(...), FILTER(...)) requires the two filters to agree, which is a common source of misalignment.
• RANK / LARGE / SMALL answer order questions without materializing a permutation.
• Readers confuse SORTBY with SORT's sort_index, and expect a by_col argument that does not exist.

Named variants

No named variants are recorded yet.