INDEX()

Returns the value of an item in a list based on its index value, or blank if the item is outside the list.

Sample usage

INDEX(Students[Name], 1) returns 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(Students[Name]). See also: ANY()

INDEX(LIST("Red", "Yellow", "Green"), 2) returns Text: Yellow

INDEX({"Red", "Yellow", "Green"}, 4) returns blank (4 is outside the list).

Highest value in column

The highest product price:

INDEX(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. INDEX(..., 1) returns the first price in the sorted list.

Equivalent to: MAX(Products[Price])

See also: MAX(), SORT()

Month number to name

Today's month name (returns Text):

INDEX(
  {
    "January", "February", "March",
    "April", "May", "June",
    "July", "August", "September",
    "October", "November", "December"
  },
  MONTH(TODAY())
)

1. {"January", ...} constructs a list of month names.
2. TODAY() returns today's date.
3. MONTH(...) converts a Date value to a number corresponding to the month of the year.
4. INDEX(..., MONTH(...)) uses the month number to choose a month name from the list.

Equivalent to:

SWITCH(
  MONTH(TODAY()),
  1, "January",
  2, "February",
  3, "March",
  4, "April",
  5, "May",
  6, "June",
  7, "July",
  8, "August",
  9, "September",
  10, "October",
  11, "November",
  12, "December",
  ""
)

See also: MONTH(), TODAY()

Preferred value

A mobile, office, or home phone number chosen from those that aren't blank:

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

1. LIST([Mobile Phone], ...) constructs a list of the three numbers.
2. LIST(...) - LIST("") removes any blank items from the list of numbers.
3. INDEX(..., 1) returns the first of the remaining items of the list.

Equivalent to:

ANY(
  TOP(
    (
      LIST([Mobile Phone], [Office Phone], [Home Phone])
      - LIST("")
    ),
    1
  )
)

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

Row with highest value in column

The row of the student with the highest GPA in Mr Sandwich's class (returns Ref):

INDEX(
  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. INDEX(..., 1) returns the first item in the ordered list, the key of the row having the highest GPA.

Equivalent to:

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

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

Weekday number to name

Today's weekday name (returns Text):

INDEX(
  {
    "Sunday", "Monday", "Tuesday",
    "Wednesday", "Thursday", "Friday",
    "Saturday"
  },
  WEEKDAY(TODAY())
)

1. {"Sunday", ...} constructs a list of weekday names.
2. TODAY() returns today's date.
3. WEEKDAY(...) converts a Date value to a number corresponding to the day of the week.
4. INDEX(..., WEEKDAY(...)) uses the weekday number to choose a weekday name.

Equivalent to:

SWITCH(
  WEEKDAY(TODAY()),
  1, "Sunday",
  2, "Monday",
  3, "Tuesday",
  4, "Wednesday",
  5, "Thursday",
  6, "Friday",
  7, "Saturday",
  ""
)

See also: TODAY(), WEEKDAY()

Syntax

INDEX(list, which-one)

• list - List of any type.
• which-one - Index (Number) of the item value to retrieve. The first item in the list is 1.

See also

ANY()

TOP()