Index() Function

Index() Function

The INDEX function is a built-in function in Excel that is categorized as a Lookup/Reference Function. The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns.

There are two formats of the function, which are the Array Format (which is the most basic format), and the Range Format of the function. These are described separately below.

Array format

The Array format of the Index function is used when you want to look up a reference to a cell within a single range.The syntax of the function is:

INDEX (array, row_num, [col_num])

Where the arguments are as follows:

Array

The specified array or range of cells.

row_num

Denotes the row number of the specified array. If set to zero or blank, this defaults to all rows in the supplied array.

[col_num]

Denotes the column number of the specified array. If set to zero or blank, this defaults to all columns in the supplied array.

Note that the row_num and the [col_num] arguments cannot both be zero or blank.

Example 1

In the following example, the Index function returns a reference to row 5 of the range C1:C5, which is cell C5. This has the value 8.

Example 2

In the following example, the Index function returns a reference to row 5 and column 2 of the range C1:D5, which is cell D5. This has the value 3.

Reference form

The Range format of the Index function can be used to extract references from ranges that are made up of more than one area. The syntax of the function is:

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

Where the arguments are as follows:

range

The specified array or range of cells.

Note: If multiple areas are input directly into the function, the individual areas should be separated by commas and surrounded by brackets - ie. ( A1:B2, C3:D4, etc).

row_num

Denotes the row number of the specified area. If set to zero or blank, this defaults to all rows of the specified area within the supplied range.

[col_num]

Denotes the column number of the specified area. If set to zero or blank, this defaults to all columns of the specified area within the supplied range.

[area_num]

If the initial specified range is made up of more than one area, the [area_num] argument specifies the number of the area to be used.

(Note that the areas are numbered by the order they are specified).

If the [area_num] argument is omitted, it defaults to the value 1 (i.e. the reference is taken from the first area in the supplied range.

Note that either (but not both) of the row_num or the [col_num] arguments may be zero or blank. If they are both zero or blank, the Index function will return an error.

Example 1

In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area in the supplied range. This is cell D4, which evaluates to the value 5.

Example 2

In the following example, the Index function returns a reference to row 3 and column 1 of the 3rd area in the supplied range. This is cell B12, which evaluates to the value 7.