INDEX formula in Excel a complete reference with example

INDEX

Function is highly resource intensive function as it comes under Volatile category. Due to Volatile nature it claims each change in a sheet and force to recalculate. To retrieve value from given position the INDEX function can be used. There are two ways to use the INDEX function:

1. Return the value of a specified cell or array of cells (Array form)
2. Return a reference to specified cells (Reference form)

Array form:

Value will be returned in a table or an array form, based on row and column number supplied in. Use the array form if the 1st argument to INDEX is an array constant.

Syntax

=INDEX(array, row_num, [column_num])

1. Array: its mandatory argument. A range of cells or an array constant:
   • If array contains only one row or column, the corresponding row_num or column_num argument can be considered as optional.
   • If array has more than one row and more than one column, and only row_num or column_num is used, INDEX returns an array of the entire row or column in array.
2. row_num: its mandatory argument. Until column_num is present. Selects the row in array from which to return a value. If row_num is removed then column_num is required.
3. column_name: its an optional argument. Selects the column in array from which to return a value. If column_num is removed then row_num is required.

To use values returned as an array, enter the INDEX function as an Array Formula ({}) following.

Data source

Refer below formula example where:

• Get Car name from 2nd row and 1st column
• Get the Model from above data source 2nd row and 2nd column

Formula should look like below:

Output

Reference form

Syntax

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

1. reference: its mandatory argument. A reference to one or more cell ranges.
2. row_num: its mandatory argument. The number of the row in reference from which reference will be returned.
3. column_num: its an optional argument. The number of the column in reference from which reference will be returned.
4. area_num: its an optional argument. Selects a range to return the intersection of row_num and column_num.

Data source

Example: get 1st Car name from 2nd area

Output

Next >> MATCH vs INDEX in Excel with example

Share on Facebook

Tweet