Using the Excel INDEX Function
SummaryThe 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 BreakdownArray
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.
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.
Optional (unless row_num is omitted). Used to specify which column should be used to return a value.
Array Form Usage NotesThe 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.
Reference Form Syntax=INDEX (reference, row_num, [column_num], [area_num])
Reference Form Syntax BreakdownReference
Required. Reference to one or more cell ranges. If you are using nonadjacent range, then you must enclose reference in parentheses.
Required. Used to specify which row in the reference should be used to return a value.
Optional. Used to specify which column should be used to return a reference.
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.
Reference Form Usage NotesThe 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.