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:
Contents
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)
Result: The value in the the second row, third column (column G) of the table is returned
You can download the workbook for example 1 and 2 here.
Example 2:
=Index(F3:G5, 3, 2)
Result: The value in the the third row (row 5), second column (column G) of the table is returned:
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:
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”:
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”:
Below you can see the formulas used in each cell:
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:
$A2: The cell where the row index is input by the user:
“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):
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):
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):
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:
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:
The value 221$ displayed in cell B3 in the figure above is equivalent to the sum of the cells B2 to B5 in sheet2:
By changing the values in cells B1 and B2 of Sheet1, the value in cell B3 will be updated accordingly:
By changing cell B1 to 3 and B2 to 9 in the figure above, cell B3 will show the sum of the cells below:
Probably 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:
- Excel VLOOKUP vs INDEX and MATCH Speed Comparison
- Excel INDEX MATCH Functions
- Excel, MATCH() function
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