Function INDEX in Excel

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 works with 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 of 1 dimension.

Change the values in D7 and D8 to change the name of the month in E7 and E8.

Explanation of the writing

To return the month of the horizontal table (in yellow), we write the fonction as following:

  1. Selection of the range of cells containing our data
  2. Write the index value (the position) that we want to return

The function is
=INDEX(A2:A13,10)
But of course, we can replace the value 10 by the reference of a cell like this

=INDEX(A2:A13,D7)

Now, if the orientation of the source is vertical (column), the writing is now like this

  1. Selection of the range of cells containing our data
  2. Leave the second parameter empty (because the orientation is vertical)
  3. Write the position or the reference that contain the value to return

=INDEX(C3:I3,,D8)

Function INDEX with 2 dimension

The INDEX function is very powerful with a 2-dimensional array. Like that, you can return the value of a cell inside a grid of data.

For instance, we want to return the salary value by using 2 parameters

  • The number of years of study (in column)
  • The number of years of work in a similar job (in row)
    =INDEX(Source, row index, column index)

It is compulsory that your selection does NOT include the headers.

For the first parameter, we just select our data (without the headers)

= INDEX(B7:G12,

For the second parameter, we are going to use the value of the number of years of study and we add 1. Because, if we want to find the row value for someone who has 3 years of University, in fact we must focus on the 4th row of our grid.
= INDEX(B7:G12,B3+1
To select the column in relation with the number of years of experience, we must use the MATCH function to return the nth element of a list.
=INDEX(B7:G12,B3+1,MATCH(B4,B6:G6,0))

Try in the workbook to change the value in B3 and B4 to change the result.

 

Related posts


Permanent link to this article: https://www.excel-exercise.com/function-index/


Leave a Reply

Your email address will not be published.