The INDEX function returns a value or the reference to a value from within a table or range.
We have prepared a tutorial on the INDEX function to explain it in a bit more detail.
The INDEX function can be used either in its Array or Reference Form
The syntax of the Array Form of the function is the following:
INDEX(array, row, [col])
array: it is required. It is a range of cells or an array.
row, col : One of them is always optional.
If the array has only one row or column then the row or col argument respectively is optional.
If the array has more than one rows or columns and only one of the two arguments (row, col) is used then the function returns an array of an entire row or an entire column.
The syntax of the Reference Form of the function is the following:
INDEX(areas, row, [col], [area_num])
areas: it is required. It is a reference to one or more ranges of cells. If you enter more than one ranges then you have to enclose them in parentheses.
row, col : One of them is always optional.
area_num: If you have entered more than one ranges in the areas argument then this arguments selects the number of the range for which you want to find the index.
If a range has only one row or column then the row or col argument respectively is optional.
If a range has more than one rows or columns and only one of the two arguments (row, col) is used then the function returns the reference to an entire row or an entire column.
Click on the button to practice using this function, with the help of our Online Assessment Tool:
Here is an example of how to use the INDEX function:
Use the proper function in cell E1 to calculate the value, of the cell in the second row and third column, of the cell range A1:D6.