Excel INDEX() Function

In this article I will explain the INDEX() function in Excel. Basically this function receives a range of cells, a row index and a column index as input. It returns the the value or reference of the cell at the specified row and column index.

Jump To:

Syntax:

=INDEX(Range, row_Index, column_Index, Area)

Range: A range of cells

row_Index: The row index of the cell value or reference desired. Note this is the row index relative to the range.

column_Index: The column index of the cell value or referenced desired. Note this is the column index relative to the range.

Area: In case multiple ranges have been chosen, this parameter selects which range is intended.

Example 1:

=Index(E1:G3, 2, 3)

Excel, Index, Example

Result: The value in the the second row, third column (column G) of the table is returned

Excel, Index, Example Result
You can download the workbook for example 1 and 2 here.


Example 2:

=Index(F3:G5, 3, 2)

Excel, Index, Example 2

Result: The value in the the third row (row 5), second column (column G) of the table is returned:

Excel, Index, Example 2 Result
You can download the workbook for example 1 and 2 here.


Example 3:

In this example it is assumed we have a database of information on sheet2.

Database in sheet2:

Excel, Index, Database Example 3

On cell A2 of sheet1 the user will input a row index. Based on the row index selected selected the values in cells C2, D2, E2 and F2 are updated. In the figure below the user has selected the row index 4. The row index 4 corresponds to the name “Emma”, age “30”, account balance “$12,484.00” and account number “1654567”:

Excel, Index, Example 3 Sheet1
In the figure below the user has input “2” in cell A2. The row index 2 corresponds to the name “John”, age “50”, account balance “$85,541.00” and account number “1654565”:

Excel, Index, Example 3 Sheet1, (2)

Below you can see the formulas used in each cell:

Excel, Index, Example 3 Formulas

Cell C2: The formula used in this cell is:

=INDEX(Sheet2!A2:A9, $A2, 1)

 

Sheet2!A2:A9:The range in Sheet2 with the names in it:

Excel, INDEX, Example 3 Sheet 2 Name Range
$A2: The cell where the row index is input by the user:

Excel, INDEX, Example 3 Cell with Row index in it
“1”: The column index in the range Sheet2!A2:A9 to retrieve the value from. Note that the range Sheet2!A2:A9 only has 1 column therefore the column index could have been omitted. The formula below would have returned the same value:

=INDEX(Sheet2!A2:A9, $A2)

Cell D2: Similar to cell C2. The input range (=INDEX(Sheet2!B2:B9, $A2, 1)) is the second column in sheet2 (the range with the ages in it):

Excel, INDEX, Example 3 Sheet 2 Age Range

The rest of the parameters are the same as cell C2.

Cell E2: Similar to cell C2. The input range (=INDEX(Sheet2!C2:C9, $A2, 1)) is the third column in sheet2 (the range with the Account Balances in it):

Excel, INDEX, Example 3 Sheet 2 Account Balance Range

The rest of the parameters are the same as cell C2.

Cell F2: Similar to cell C2. The input range(=INDEX(Sheet2!D2:D9, $A2, 1)) is the third column in sheet2 (the range with the Account Numbers in it):

Excel, INDEX, Example 3 Sheet 2 Account Number Range

The rest of the parameters are the same as cell C2. You can download the workbook for example 3 here.


Example 4:

In this example it is assumed we have the following data in sheet2:

Excel, INDEX, Example 4, Payments
As you can see in the figure above, in column B there are payment values and in column B there is the date when each payment was made. What we want to do, is to be able to choose a starting and ending row index, and to find the total sum of payments made between those 2 rows:

Excel, Index, Example 4 Sum of payments

The value 221$ displayed in cell B3 in the figure above is equivalent to the sum of the cells B2 to B5 in sheet2:

Excel, Index, Sum of Cells B2 to B5 Sheet 2
By changing the values in cells B1 and B2 of Sheet1, the value in cell B3 will be updated accordingly:

Excel, Index, Sheet1, New values
By changing cell B1 to 3 and B2 to 9 in the figure above, cell B3 will show the sum of the cells below:

Excel, Index, Sheet2, New SumProbably wondering how this was done. This can be achieved by using the formula below in cell B3 in sheet1:

=SUM(INDEX(Sheet2!B2:B10, B1, 1):INDEX(Sheet2!B2:B10,B2, 1))

The syntax for the SUM function is:

=SUM(Cell1:Cell2)

So basically instead of Cell1 I’ve used:

INDEX(Sheet2!B2:B10, B1, 1)

and instead of Cell2 I’ve used:

INDEX(Sheet2!B2:B10, B2, 1)

As you can see in this example the INDEX function is returning a reference to a cell rather than the cell value as in the previous examples.

Sheet2!B2:B10: The range which the payments are located in:

=SUM(INDEX(Sheet2!B2:B10, B1, 1):INDEX(Sheet2!B2:B10,B2, 1))

B2 and B1: References to the values in cells B1 and B2 in sheet1. They contain the row index of the cell which the INDEX() function is supposed to return a reference to:

=SUM(INDEX(Sheet2!B2:B10, B1, 1):INDEX(Sheet2!B2:B10,B2, 1))

The number “1”: The column INDEX from which the INDEX function is supposed to return the cell reference from. Since the input range has only one column, the number “1” could have been omitted from the input parameters.

For the first example above where B1 = 1 and B2 = 4 the formula

=SUM(INDEX(Sheet2!B2:B10, B1, 1):INDEX(Sheet2!B2:B10,B2, 1))

would be equivalent to:

=SUM(Sheet2!B2:Sheet2!B5)

and for the second example where B1 = 3 and B2 = 9 the formula

=SUM(INDEX(Sheet2!B2:B10, B1, 1):INDEX(Sheet2!B2:B10,B2, 1))

would be equivalent to:

=SUM(Sheet2!B4:Sheet2!B10)

You can download the workbook for example 4 here.

See also:

If you need assistance with your code, or you are looking to hire a VBA programmer feel free to contact me. Also please visit my website www.software-solutions-online.com

Leave a Reply

Your email address will not be published. Required fields are marked *