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)
Products[Price]
retrieves the list of all values from the Price column of the Products table.SORT(..., TRUE)
orders the list of prices numerically in descending/high-to-low order (TRUE
).INDEX(..., 1)
returns the first price in the sorted list.
Equivalent to: MAX(Products[Price])
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())
)
{"January", ...}
constructs a list of month names.TODAY()
returns today's date.MONTH(...)
converts a Date value to a number corresponding to the month of the year.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",
""
)
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
)
LIST([Mobile Phone], ...)
constructs a list of the three numbers.LIST(...) - LIST("")
removes any blank items from the list of numbers.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
)
FILTER("Students", ...)
returns a list of key values from the Students table that match a condition.AND(..., ...)
limits the filter to only those rows that match all of the given sub-conditions.ISNOTBLANK([Teacher])
requires the Teacher column value not be blank.[Teacher] = "Mr Sandwich"
requires the Teacher column value be exactly Mr Sandwich.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.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())
)
{"Sunday", ...}
constructs a list of weekday names.TODAY()
returns today's date.WEEKDAY(...)
converts a Date value to a number corresponding to the day of the week.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",
""
)
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.