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