Examples

ORDERBY(Products[Product ID], [Product Name]) : Returns a list of Product ID column values (the data set's key values) sorted by their corresponding Product Name column values.

ORDERBY(Products[Product ID], [Product Price], TRUE, [Product Name]) : Sorts the product rows by price in descending (high-to-low/9-0/Z-A) order (per TRUE). Rows with identical prices are further sorted by product name in the default, ascending (low-to-high/0-9/A-Z) order.

ORDERBY(SELECT(Customers[Customer ID], ([Country] = "Brazil")), [Customer Name]) : Sorts the IDs of customers in Brazil by customer name. Equivalent to ORDERBY(FILTER("Customers", ([Country] = "Brazil")), [Customer Name]). See also: FILTER(), SELECT().

Row with Maximum Value

INDEX(
  ORDERBY(
    FILTER(
      "Students",
      ([Class of] = "2018")
    ),
    [GPA],
    TRUE
  ),
  1
)
  1. FILTER("Students", ([Class of] = "2018")) gathers rows from the Students data set for the class of 2018.
  2. ORDERBY(..., [GPA], TRUE) sorts the filtered rows by their corresponding GPA column values in descending (high-to-low) order (per TRUE).
  3. INDEX(..., 1) extracts the first item (the row of the student with the highest GPA) from the sorted list.

Equivalent to MAXROW("Students", "GPA", ([Class of] = "2018")).

See also: FILTER(), INDEX(), MAXROW()

Syntax

ORDERBY( row-keys , sort-key [ , descending-order? [ , sort-column ] ]... )

Arguments

  • row-keys (List of Ref): a list of key column values for the rows to be sorted, commonly as generated with FILTER() or SELECT().
  • sort-key (expression): an expression that produces a sort key for the row. The sort key is compared against the corresponding sort keys of other rows to determine where the row will occur in the sorted list. The expression is evaluated in the context of the row being considered (similar to the match expressions in FILTER() and SELECT()). The simplest and most common sort-key expression is a reference to the column by which to sort (e.g., [Product Name] or [Start Date]), but can be more complex.
  • descending-order? (Yes/No): FALSE to sort by the immediate preceding sort-key in ascending (low-to-high/0-9/A-Z) order, or TRUE to sort in descending (high-to-low/9-0/Z-A) order.

Rows may be sorted by multiple sort keys: each additional sort key expression must be separated from the previous by an intervening descending-order? argument.

The final descending-order? argument is optional if its value would be FALSE.

Return Value

List of Ref: The original row-keys values, sorted as directed.

See Also

FILTER(), MAXROW(), MINROW(), SORT()

Did this answer your question?