INDEX function

INDEX(array, index) INDEX(array; index)

array — { ? }

The array from which an element should be retrieved.

index — Number or { Number }

The position of the element to return, starting with 1.

Returns — ? or { ? }

The array element at the given position.

Returns the array element at the given position. INDEX({ 10, 20, 30 }, 2)INDEX({ 10; 20; 30 }; 2) returns the second element, 20.

INDEX, MATCH and XMATCH

INDEX is often used together with MATCH and XMATCH to look up values from another array. MATCH and XMATCH are used to find a value in an array and return its position, where the position is then given to INDEX, which returns the array element at that position. Consider this formula:

XMATCH(20, { 10, 20, 30 })XMATCH(20; { 10; 20; 30 })

The formula above returns 2, because the sought value, 20, is found at the second position in the { 10, 20, 30 }{ 10; 20; 30 } array.

As such, this formula returns 2 if the value of Field1 is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3:

INDEX({ 2, 4, 8 }, XMATCH(Field1, { 1, 2, 3 }))INDEX({ 2; 4; 8 }; XMATCH(Field1; { 1; 2; 3 }))

This XLOOKUP formula is equivalent:

XLOOKUP(Field1, { 1, 2, 3 }, { 2, 4, 8 })XLOOKUP(Field1; { 1; 2; 3 }; { 2; 4; 8 })

INDEX and SIZE

The SIZE function returns the number of elements in an array. It can be used together with INDEX to return array elements from the end of the array.

This formula returns the second array element:

INDEX(Field1:Field3, 2)INDEX(Field1:Field3; 2)

This formula returns the second to last array element from the Field1:Field3Field1:Field3 array:

INDEX(Field1:Field3, SIZE(Field1:Field3) - 1)INDEX(Field1:Field3; SIZE(Field1:Field3) - 1)

The Field1:Field3Field1:Field3 range is a short-hand way of expressing an array containing Field1 and Field3, as well as any fields that appear between them, such as Field2. If only Field2 appears between the other two fields, Field1:Field3Field1:Field3 and { Field1, Field2, Field3 }{ Field1; Field2; Field3 } are equivalent.

Multiple results

Multiple elements can be retrieved at the same time using INDEX, by supplying an array as the second parameter. This formula returns the first and third elements as an array, { 10, 30 }{ 10; 30 }:

INDEX({ 10, 20, 30 }, { 1, 3 })INDEX({ 10; 20; 30 }; { 1; 3 })

Examples

INDEX({ 10, 20, 30 }, 2)INDEX({ 10; 20; 30 }; 2)

Returns the second element, 20.

INDEX({ 10, 20, 30 }, { 1, 3 })INDEX({ 10; 20; 30 }; { 1; 3 })

Returns the first and third elements as an array, { 10, 30 }{ 10; 30 }.

INDEX(Field1:Field20, SIZE(Field1:Field20))INDEX(Field1:Field20; SIZE(Field1:Field20))

Returns the last element from the Field1:Field20Field1:Field20 range, using the SIZE function, which returns the number of elements in an array.

INDEX({ 2, 4, 8 }, XMATCH(Field1, { 1, 2, 3 }))INDEX({ 2; 4; 8 }; XMATCH(Field1; { 1; 2; 3 }))

Returns 2 if the value of Field1 is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3.

XLOOKUP(Field1, { 1, 2, 3 }, { 2, 4, 8 })XLOOKUP(Field1; { 1; 2; 3 }; { 2; 4; 8 })

Returns 2 if the value of Field1 is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3. Combining INDEX and MATCH is often equivalent to using XLOOKUP.