INDEX

Using the Excel INDEX Function

Summary

The INDEX function is used to return a value at a given position in a range or an array. INDEX can retrieve an individual value or an entire row or column. The function can be used in either an array or reference form.

Array Form Syntax

=INDEX (array, row_num, [column_num])

Array Form Syntax Breakdown

Array
Required. This argument can be a range of cells or an array constant. If your array only has one row or column, the remaining row_num and column_num arguments are optional.

Row Num
Required. Used to specify which row in the array should be used to return a value. If this argument is omitted, then you must enter a value for column_num.

Column Num
Optional (unless row_num is omitted). Used to specify which column should be used to return a value.


Array Form Usage Notes

The INDEX array form is used to return a value found in a specific cell or array of cells.

If you provide values for both the row_num and column_num, the function will return the value in the cell at the intersection of the two.

If row_num is zero, INDEX will return an array of values for the entire row. Likewise, if column_num is set to zero, the function returns an array of values for the entire column. To use the returned array values you can feed the array into another function.

Row_num and column_num must specify a cell that is in the array. If an outside cell is specified, INDEX will return the #REF! error.

Excel INDEX Array REF Error


Reference Form Syntax

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

Reference Form Syntax Breakdown

Reference
Required. Reference to one or more cell ranges. If you are using nonadjacent range, then you must enclose reference in parentheses.

Row Num
Required. Used to specify which row in the reference should be used to return a value.

Column Num
Optional. Used to specify which column should be used to return a reference.

Area Num
Optional. The range in a reference that should be used to return the intersection of row_num and column_num. This argument is supplied as a number. For example, =INDEX((A1:D5, F1:H5),3,2,1) has area_num set to 1. This refers to the first range of A1:D5.

Excel INDEX Reference Form Example


Reference Form Usage Notes

The reference form of the INDEX function is used to return the reference of a cell at the intersection of row and column. Users have the ability to specify which selection to look in if reference is made up of nonadjacent selections.

Similar to the array form row_num, column_num, and area_num must all point to a cell within the reference. If the specified cell is outside of the reference, the function returns the #REF! error.