# 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:

### 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 ### Create a progress bar in Excel using VBA (Visual Basic for Applications) ### Get Count of Cells based on Color Excel Add-in using VBA ### Create your own salary slip in Excel with free Template ### TRUNC Function or TRUNC Formula in Excel with example 