Syntax

INDEX(some-list, which-one)

Arguments

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

Return Value

The value of item which-one in some-list, or blank if which-one is outside the list.

Examples

INDEX(Students[Name], 1) : 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: INDEX(LIST("January", "February", "March", "April", "May", "June", "July", "August", "September", "October", "November", "December"), MONTH(TODAY())) (returns Text)

  1. TODAY() returns today's date.
  2. MONTH(...) converts a Date value to a number corresponding to the month of the year.
  3. INDEX(LIST(...), MONTH(...)) uses the month number to choose a month name.

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], [Office Phone], [Home 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: INDEX(ORDERBY(FILTER("Students", AND(ISNOTBLANK([Teacher]), ([Teacher] = "Mr Sandwich"))), [GPA], TRUE), 1) (returns Ref)

  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: INDEX(LIST("Sunday", "Monday", "Tuesday", "Wednesday", "Thursday", "Friday", "Saturday"), WEEKDAY(TODAY())) (returns Text)

  1. TODAY() returns today's date.
  2. WEEKDAY(...) converts a Date value to a number corresponding to the day of the week.
  3. INDEX(LIST(...), 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()

See Also

ANY(), TOP()

Did this answer your question?