Function Reference: INDEX
| Syntax | ||
| Reference Form =INDEX(reference,row_num,[col_num],[area_num]) Array Form =INDEX(array,row_num,[col_num]) |
||
| Purpose / Description | ||
| The reference form returns a reference to a cell or range of cells from within the given reference. The array form returns a value or an array of values from within the given array. |
||
| Arguments | ||
| Name | Type | Description |
| reference | reference | A reference to a range, or a nested expression that returns a reference to a range, from within which the result will be found and returned. Applies to the reference form of the function only. To specify a multi-area reference which isn't named, use a Union operation inside parentheses. |
| array | array of any | A constant, constant array or nested expression that returns an array of values from which the result or results are to be returned. Applies to the array form of the function only. |
| row_num | number | A constant, reference or nested expression that resolves to the row number in which the result is found. If omitted or 0, in a two-dimensional range (reference form) or two-dimensional array (array form) an entire column will be returned. Any decimal portion is ignored. |
| [col_num] | number | An optional constant, reference or nested expression that resolves to the column number in which the result is found. If omitted or 0, in a two-dimensional range (reference form) or two-dimensional array (array form) an entire row will be returned. Any decimal portion is ignored. |
| [area_num] | number | For the reference form of the function only, an optional constant, reference or nested expression that resolves to the area number within a multi-area range reference in which the result is found. Any decimal portion is ignored. Defaults to 1 if omitted. |
| Return Values | ||
| Type | Specific Value | Description |
| reference | For the reference form of the function, a reference to the cell or cells from within the original reference argument which occupy the given row and/or column and area (if provided for a multi-area reference). If row_num = 0 or omitted and col_num is non-zero, this returns a reference to the entire column of the reference. If col_num is 0 or omitted and row_num is non-zero, this returns a reference to the entire row of the reference. If both row_num and col_num are 0, the original reference or area of reference is returned. |
|
| any | For the array form of the function, a value or array of values from within the original array argument which occupy the given row and/or column. If row_num = 0 or omitted and col_num is non-zero, this returns an array containing the contents of the entire column of the array. If col_num is 0 or omitted and row_num is non-zero, this returns an array containing the contents of the entire row of the array. If both row_num and col_num are 0, the original array is returned. |
|
| error | #VALUE! | If any of row_num, col_num or area_num are not numbers and cannot be coerced to numbers If row_num or col_num <0. If area_num is < 1. |
| error | #REF! | If row_num, col_num or area_num are respectively greater than the number of rows, columns or areas in the reference or array. |
| error | Any errors referenced by arguments are returned due to propagation of error values. | |
| Notes / Comments / Advice / Warnings | ||
| Advice: | Unless only one value is to be retrieved or referenced, we recommend not nesting the MATCH or XMATCH function or functions in an INDEX expression, to streamline, improve transparency and improve performance. | |
| Examples: | Open / download sample workbook: INDEX.xlsx | |