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:

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 SoughtValue is equal to 1, 4 if it is equal to 2 and 8 if it is equal to 3:

This XLOOKUP formula is equivalent:

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:

This formula returns the second to last array element from SomeArray :

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 }:

Returns the element at the given row and column position in a two-dimensional array. INDEX ({{ 10, 20 }, { 30, 40 }}, 2, 1) INDEX ({{ 10; 20 }; { 30; 40 }}; 2; 1) returns 30, the element in row 2 and column 1.