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)
Products[Price]
retrieves the list of all values from thePrice
column of theProducts
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 aDate
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 theStudents
table that match a condition.AND(..., ...)
limits the filter to only those rows that match all of the given sub-conditions.ISNOTBLANK([Teacher])
requires theTeacher
column value not be blank.[Teacher] = "Mr Sandwich"
requires theTeacher
column value be exactlyMr Sandwich
.ORDERBY(..., [GPA], TRUE)
orders the filtered keys by the values of their correspondingGPA
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 aDate
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(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.