↑ Return to Find

INDEX

 

The INDEX function returns the contain of a cell at the intersection of a row and a column in a table or a reference.

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

=INDEX(A2:A13,10)

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

=INDEX(C3:I3,,4)

 

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.

=INDEX(B7:G12,B3,B4)

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.

= INDEX(B7:G12,B3+1

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.

=INDEX(B7:G12,B3+1,MATCH(B4,B6:G6,0))

Multi-Array

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:

=INDEX((A2:D5,F4:I5,B9:E11),2,4,H8)

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.

2 comments

  1. Jamlams

    Returns a reference function doesnt work. Can anyone go through it and see where it has gone wrong please?

    Cheers

  2. Jim

    In Excel 2013, how do I hold a cells formula if a column is inserted in between the formula cell and the data cells? Example, A1 contains the formula =B1+C1. Cell B1 contains data (ex ‘2’), Cell C1 contains data (ex ‘3’). When a new column is inserted in between Column ‘A’ and ‘B’, the value of cell A1 will change to C1+D1, which I need to remain as originally written as =B1+C1.
    Dollar signs ($) placed in the cell A1 does not hold the values (ex =$B1+$C1).
    Does anyone have any answers?
    Please Help.

Leave a Reply

%d bloggers like this: