INDEX function

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 XMATCH is often equivalent to using XLOOKUP.

INDEX(Employees!Name, XMATCH(MAX(Employees!Salary), Employees!Salary))INDEX(Employees!Name; XMATCH(MAX(Employees!Salary); Employees!Salary))

Returns the name of the employee with the highest salary. Name and Salary are both named values, belonging to the Employees screen, whose formulas are arrays specifying the contents of these table columns. These named values and their formulas can be created and maintained using a spreadsheet-like editor in Calcapp Creator.