What is INDEX Function in MS Excel?

The value itself or the reference to the value is returned by using the INDEX function in MS Excel. This function can be used in two ways. If you want the returned value from a specific cell or array of cells, then you use the array form. If you want the returned value from a reference to a specific cell, then use the reference form.

Purpose of INDEX Function in Excel

The purpose of this function in Excel is to return a value from a specific cell or range of cells within a table or array. The function has two forms which are described below.

Array Form: if the first argument in the INDEX function is an array consonant, then array form will be used.

Reference Form: when you want the reference of the cell at the intersection of a particular row or column, then this form will be used.  

Syntax of INDEX Function

The syntax of the INDEX function is as follows.

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

=INDEX (reference, row_num, [column_num], [area_num])

Arguments

The arguments in this function are described below.

Array: this is the range of cells.

Reference: this is the reference given to specific cells or a range of cells.

Row_num: this is the position of the row in an array or reference.

Col_num: this is the position of the column in a reference or array.

Area_num: this is the range of the given reference.

Examples

=INDEX (B5:E13, 5, 3)

B5:E13 is the array or cell reference. 5 and 3 are the column and row numbers respectively.