- Advanced Excel Functions Tutorial
- Advanced Excel Functions - Home
- Compatibility Functions
- Advanced Excel Functions - Cube
- Database Functions
- Date & Time Functions
- Engineering Functions
- Financial Functions
- Information Functions
- Advanced Excel Functions - Logical
- Lookup & Reference Functions
- Math & Trignometric Functions
- Statistical Functions
- Useful Resources
- Quick Guide
- Useful Resources
- Discussion
Lookup and Reference - INDEX Function
Description
The INDEX function returns a value or the reference to a value from within a table or range. You can use INDEX function in two ways −
- To return the value of a specified cell or array of cells.
- To return a reference to specified cells.
Using INDEX function to return the value of a specified cell or array of cells
Use this if the first argument to INDEX is an array constant.
Description
The function returns the value of an element in a table or an array, selected by the row and column number indexes.
Syntax
INDEX (array, row_num, [column_num])
Arguments
Argument | Description | Required/ Optional |
---|---|---|
Array |
A range of cells or an array constant. If array contains only one row or column, the corresponding Row_num or Column_num argument is 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. |
Required |
Row_num | Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required. |
Required |
Column_num | Selects the column in array from which to return a value. If Column_num is omitted, Row_num is required. |
Optional |
Notes
If both the Row_num and Column_num arguments are used, INDEX returns the value in the cell at the intersection of Row_num and Column_num.
If you set Row_num or Column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. To use values returned as an array, enter the INDEX function as an array formula in a horizontal range of cells for a row, and in a vertical range of cells for a column. To enter an array formula, press CTRL+SHIFT+ENTER
Row_num and Column_num must point to a cell within array. Otherwise, INDEX returns the #REF! error value.
Using INDEX function to return a reference to specified cells
Description
The function returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.
Syntax
INDEX (reference, row_num, [column_num], [area_num])
Arguments
Argument | Description | Required/ Optional |
---|---|---|
Reference |
A reference to one or more cell ranges. If you are entering a nonadjacent range for the reference, enclose reference in parentheses. If each area in reference contains only one row or column, the Row_num or Column_num argument, respectively, is optional. E.g. for a single row reference, use − INDEX(reference,,column_num) |
Required |
Row_num | The number of the row in reference from which to return a reference. |
Required |
Column_num | The number of the column in reference from which to return a reference. |
Optional |
Area_num | Selects a range in reference from which to return the intersection of Row_num and Column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If Area_num is omitted, INDEX uses area 1. |
Optional |
Notes
After Reference and Area_num have selected a particular range, Row_num and Column_num select a particular cell: Row_num 1 is the first row in the range, Column_num 1 is the first column, and so on. The reference returned by INDEX is the intersection of Row_num and Column_num.
If you set Row_num or Column_num to 0 (zero), INDEX returns the reference for the entire column or row, respectively.
Row_num, Column_num, and Area_num must point to a cell within reference. Otherwise, INDEX returns the #REF! Error value. If Row_num and Column_num are omitted, INDEX returns the area in reference specified by Area_num.
The result of the INDEX function is a reference and is interpreted as such by other formulas. Depending on the formula, the return value of INDEX may be used as a reference or as a value. For example, the formula CELL("width",INDEX(A1:B2,1,2)) is equivalent to CELL("width",B1). The CELL function uses the return value of INDEX as a cell reference. On the other hand, a formula such as 2*INDEX(A1:B2,1,2) translates the return value of INDEX into the number in cell B1.
Applicability
Excel 2007, Excel 2010, Excel 2013, Excel 2016