Presentation of the function INDEX
The INDEX function must have three parameters:
- References of a range of cell containing the value to return
- The row index
- The column index
- [Optional] zone number (in case of multi-tables)
The order of parameters is very important. Often, users reverse the setting of the row and the column and inevitably, the function does not return the correct result.
In the example below, we have two tables in one dimension. Change values in D7 and D8 to change results in E7 and E8.
In a table column, we learn the second parameter to return one of the array elements, such as the 10th in this formula
And a table online, we have to leave the second parameter empty and fill in the third to return a value in the table, by the fourth in this formula
Function INDEX with 2 dimension
But the interest of the INDEX function is to work in a 2-dimensional array by filling the two parameters of line and column. Take the example of the pay scale.
We want to get the salary level by using values of the headers (line and column) of our table. So we will use the INDEX function to stating for example that we will get the value at the intersection of the fourth row and second column.
Very important point: it is compulsory that your array does NOT include the header row and column.
But ideal would be to use values contained in the header row or column to extract a result. For lines, this is not very difficult. Just add an index to the table and our formula works.
But for columns, it does not work because values are not linear. So we must use the MATCH function to return the nth element of a list.
The INDEX function allows to search inside multiple tables in the same function and there, it is compulsory to fill the fourth parameter of the function to indicate the array read.
Here we have 3 tables which have not the same size and we want to retrieve information from row the 2 and the column 4 of one of these tables. In cell H8, you fill the value 1, 2 or 3 corresponding to the array number. As we have several tables, we need to write their references in parentheses and then we write following formula:
Returns a reference
Previously, we have seen how the function returns a value but the INDEX function can also returned the reference of a cell (What are you talking about man 😐 ).
You will never see the INDEX function returning a cell’s reference like A1, C4, D8, B2, …. But, we can illustrate this functionality by integrating the INDEX function in a SUM function.
In the example below, you have a document which summarizes your sales of the year. And when you change the month in F2, automatically, your SUM functions calculate from January to your month in F2.