This function selects a value from a list or table by searching down the provided rows and across the specified columns.

Syntax:

=INDEX (array, row_num, [col_num], [area_num])

Parameter list:

• array – a collection of cells, also known as an array constant.
• row number – the row number in the reference or array.
• col num – [optional] the location of the column in the reference or array.
• area number – [optional] the reference range

The INDEX function has two forms: Array and Reference.

1. Array form

The first parameter in the array form of INDEX is array, which can be provided as a range of cells or an array constant.

Syntax:

INDEX (array, row_num, [col_num])

• INDEX returns the value in the cell at the intersection of row num and col num if both are given.
• INDEX delivers an array of values for the whole row if row num is set to zero. Enter the INDEX function as an array formula in horizontal range to utilise these array values.
• INDEX delivers an array of values for the whole column if col num is set to zero. Enter the INDEX function as an array formula in vertical range to utilise these array values.

2. Reference form

The first argument in the reference form of INDEX is reference, which is given as a reference to one or more cell ranges.

Syntax:

INDEX (reference, row_num, [col_num], [area_num])

• The INDEX reference form returns the cell reference at the intersection of row num and col num.
• If reference is provided as a list of ranges, area num determines the range to utilise.
• The value of area sum is given as a number. In the formula =INDEX((A1:D5,A7:D10),2,2,2), for example, area num is provided as 2, referring to the range A7:D10.