How to use INDEX in Excel

Functions like VLOOKUP, INDIRECT, or MATCH allow you to work with data ef­fi­cient­ly. With Excel INDEX, you can quickly and easily return the value of a cell.

Quick guide for Excel INDEX

1. Decide where Excel INDEX should be applied.
2. In two separate cells, specify values for row and column to determine the cell whose value you want to return.
3. In a third cell, specify the Excel INDEX function: =INDEX(Array; Row; Column).

What is Excel INDEX used for?

With the help of Excel INDEX, you can reproduce the content of any cell in another cell. This is why the formula contains co­or­di­nates. Based on the column and row specified, the function de­ter­mines the desired value from an area that is defined by the user. This can then be shown directly in another cell or used in another function.

Since the spec­i­fi­ca­tion is done through columns and rows, the Excel INDEX function is also very useful when working with arrays. In an array, Excel can read out the cor­re­spond­ing value for two specified factors.

What is the syntax for Excel INDEX?

The function uses a clear syntax. The user enters the area to be searched as a parameter and then the co­or­di­nates of the desired cell.

=INDEX(array; row; column)

The following pa­ra­me­ters are used in the function:

• Array, defined by the top-left and bottom-right cell
• Row, in which the desired cell is located
• Column, in which the desired cell is located

The array is always defined using two cells, separated by a colon. If it only contains a single column or a single row, you only need to include one ad­di­tion­al parameter. Excel INDEX will au­to­mat­i­cal­ly un­der­stand whether you mean the cor­re­spond­ing row or column.

You can either enter the co­or­di­nates directly as a number or establish a cell reference. The numbers are then contained in the cells specified. The parameter always relates to the selected area and not to the worksheet, for example. So, if the array begins in cell C2, the D column is number 2 and not number 4.

Practical examples of how to use the Excel INDEX function

The simplest way to use the Excel INDEX function is to reproduce the content of a specific cell. Cell ref­er­ences can be used to make working with the formula a little easier. This way, you can enter the column and row pa­ra­me­ters directly in a cell and dy­nam­i­cal­ly adjust the formula.

=INDEX(B2:E10;B12;B13)

We entered the column and row pa­ra­me­ters in the cells B12 and B13. In this example, it’s important to note that the area within our Excel INDEX formula does not include the outer labels (name and birth year). Therefore, the correct result in our case is the first entry for Louise (“42”), which is in line 3 column 1 of the specified range.

Close

You can expand the formula if you have multiple tables with the same structure. Let’s assume we have four tables – one for every quarter. The structure of the tables is the same, meaning the columns and rows are laid out iden­ti­cal­ly.

=INDEX((C4:F7;I4:L7;C11:F14;I11:L14);C16;C17;C18)

We have the pa­ra­me­ters for rows, columns, and the area via cell ref­er­ences. To make it easier, you can also create Excel drop-down lists for the parameter selection. Excel will then create a helpful form for selecting the correct value with the Excel INDEX function.

Close

How to combine Excel INDEX with other functions

With Excel Index, you can also combine math­e­mat­i­cal or sta­tis­ti­cal functions such as AVERAGE. In this case, INDEX (again in con­junc­tion with MATCH) indicates the area for which the mean value is to be cal­cu­lat­ed. Rather than inserting it directly into the formula, you can dy­nam­i­cal­ly change the selection area via the arrange­ment of the formula.

=AVERAGE(INDEX(B2:E10;MATCH(B12;A2:A10;0);0))

For the value in cell B12 (in the example: “Frank”), you get the re­spec­tive mean value. This technique also works well when combined with the Excel SUM function.

Close