Syntax

ANY(some-list)

Arguments

  • some-list (list of any type)

Return Value

The first item from some-list if the list is constructed (e.g., using LIST()), or an arbitrary item if some-list is a generated list (e.g., using FILTER() or SELECT()); or blank if some-list is empty.

Examples

ANY(Students[Name]) : An arbitrary value from the Name column of the Students table. Arbitrary because the order of values in the Students[Name] column list isn't guaranteed unless wrapped in SORT(). Equivalent to ANY(SELECT(Students[Name], TRUE)). See also SELECT().

ANY(LIST(1, 2, 3)) returns Number: 1

ANY({"Red", "Yellow", "Green"}) returns Text: Red

Column Value

A single column value from any of a set of rows: ANY(SELECT(Products[Price], ([Color] = "Orange")))

  1. SELECT(Products[Price], ...) returns values in the Price column from rows in the Products table that match the selection criteria.
  2. [Color] = "Orange" limits the selection to only those rows with a Color column value of exactly Orange.
  3. ANY(...) returns one arbitrary value from the list of column values.

Equivalent to: LOOKUP("Orange", "Products", "Color", "Price")

See also: LOOKUP(), SELECT()

Highest Value in Column

The highest product price: ANY(TOP(SORT(Products[Price], TRUE), 1))

  1. Products[Price] retrieves the list of all values from the Price column of the Products table.
  2. SORT(..., TRUE) orders the list of prices numerically in descending/high-to-low order (TRUE).
  3. TOP(..., 1) removes all but the first price in the sorted list.
  4. ANY(...) returns the one remaining price from the top list.

Equivalent to MAX(Products[Price]).

See also: MAX(), SORT(), TOP()

Preferred Value

A mobile, office, or home phone number chosen from those that aren't blank: ANY(TOP((LIST([Mobile Phone], [Office Phone], [Home Phone]) - LIST("")), 1))

  1. LIST([Mobile Phone], [Office Phone], [Home Phone]) constructs a list of the three numbers.
  2. LIST(...) - LIST("") removes any blank items from the list of numbers.
  3. TOP(..., 1) removes all but the first from the list of non-blank numbers.
  4. ANY(...) returns the only remaining non-blank number from the top list.

Equivalent to INDEX((LIST([Mobile Phone], [Office Phone], [Home Phone]) - LIST("")), 1).

See also: LIST(), INDEX(), TOP()

Row with Highest Value in Column

The row of the student with the highest GPA in Mr Sandwich's class: ANY(TOP(ORDERBY(FILTER("Students", AND(ISNOTBLANK([Teacher]), ([Teacher] = "Mr Sandwich"))), [GPA], TRUE), 1))

  1. FILTER("Students", ...) returns a list of key values from the Students table that match a condition.
  2. AND(..., ...) limits the filter to only those rows that match all of the given sub-conditions.
  3. ISNOTBLANK([Teacher]) requires the Teacher column value not be blank.
  4. [Teacher] = "Mr Sandwich" requires the Teacher column value be exactly Mr Sandwich.
  5. ORDERBY(..., [GPA], TRUE) orders the filtered keys by the values of their corresponding GPA column value in descending/high-to-low order (TRUE), putting high GPAs first.
  6. TOP(..., 1) removes all but the first item in the ordered list, leaving only the key of the row having the highest GPA.
  7. ANY(...) returns the one remaining entry from the top list: the key of the row corresponding to the student with the highest GPA in Mr Sandwich's class.

Equivalent to MAXROW("Students", "GPA", AND(ISNOTBLANK([Teacher]), ([Teacher] = "Mr Sandwich"))).

See also: AND(), FILTER(), ISNOTBLANK(), ORDERBY(), MAXROW(), TOP()

Potential Problems

ANY(1, 2, 3) : the arguments are not in list form. To fix, wrap them in LIST() to construct a list: ANY(LIST(1, 2, 3)).

See Also

INDEX(), TOP()

Did this answer your question?