INDEX()

One specific list item

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()

Was this helpful?
How can we improve it?
Search
Clear search
Close search
Google apps
Main menu
Search Help Center
false
false
false
false