Index function in Excel

What is INDEX function?

The INDEX function within a table is used to return a value or the reference value in the given range or array. It means that there are two formats of the INDEX function. It is given as:

• Array format
• Reference format

Array format

The array format is used when we want to return a value of a specified array.

The formula of the array format is given by:

INDEX(array, row_num, [col_num])

Where,

Array: It is the specified array of cells.

row_num: It denotes the number of rows in an array of cells.

col_num: It denotes the number of columns in the array of cells.

Column number of the INDEX function is optional. It can be set to zero or back if there is no requirement of the column in the function.

Reference format

The array format is used when we want to return a value with the reference of the cell.

The formula of the reference format is given by:

INDEX(reference, row_num, [col_num], [area_num])

Where,

Reference: It is the reference of one or more cells in the function. We can insert single or multiple areas directly into the function. But, the individual areas should be enclosed in the brackets and separated by the commas.

row_num: It denotes the number of rows in an array of cells.

col_num: It denotes the number of columns in the array of cells.

area_num: The area number indicates a particular range to use from the multiple ranges specified in the INDEX function.

The INDEX function is not same as the vlookup function in excel. But, we can say that it can be used as an alternative way in place of the vlookup command. The

Purpose of using the INDEX function

We have discussed the index function and its two formats. But, it is used for which purpose? The index function is used to find a particular value in a list of tables. Inside the function, we can specify the position of the value that needs to be located because it is difficult to locate a particular value in a table with large data.

What value do the INDEX() returns?

The INDEX function returns the value at a specified location. It means that whatever location we have declared inside the function (such as row number, column number) will return the specified location’s value.

How INDEX function works?

Let’s discuss it with the help of an example. Consider the below table.

Let find the value 53 from the given table. The steps are as follows:

1. Click on any cell outside the given table.
2. Now, we have to type the INDEX function, which is computed as INDEX(reference, row_num, col_num).
   • For reference, enclose the data of the given table with the help of a mouse starting from the first element of the table to the last element, as shown below:
     
     Here, the reference according to the selected table will be C4:D5.
3. Type the row number and the column number where the element to be found is present.
4. The row number and column number are measured with respect to the selected table for reference. It means that the two and column number does not depends on the numbers on the left bar of the excel sheet.
5. Drag the mouse on the formula bar and compute the value inside the INDEX function, as discussed in the Step 2. It will appear on the formula bar as: ‘=INDEX(C4:D5,2,2).’
   
6. Press Enter. The value will appear in the specified cell, which is the result of the intersection of the specified row and column. Thus, 53 is the value where row number 2 and column number 2 intersects.

Similarly, other values of the table can be represented as:

• ‘=INDEX(C4:D5,1,1)’

Value = 0.6

Intersection of row1 and column1

• ‘=INDEX(C4:D5,1,2)’

Value = 76

Intersection of row1 and column2

• ‘=INDEX(C4:D5,2,1)’

Value = 14

Intersection of row2 and column1

If there is cell space between the two rows and columns, it will be considered as a blank row or column. So, we need to count that row and column also.

For example,

The above table has two columns and three rows. It is because of the one cell space between the two columns and rows, which is considered by default. Hence, we should avoid spaces in between.

INDEX function can be used to restore individual values or the entire row or column. We will discuss the examples later in the topic.

Relation between INDEX and SUM function

As discussed, the INDEX function is used to find a value in the given array or table. The SUM function is used to calculate the sum of the specified values. But, what is the relation between these two?

The INDEX function declared inside the SUM function can calculate the sum of the value specified in the declared row or column. It is given by:

SUM( INDEX (reference, row_num, [col_num])

Let’s discuss an example. Consider the below table.

Here, we will calculate the sum of all the numbers present in the table. Consider the below steps:

1. Click on any cell outside the given table.
2. Now, we have to type the INDEX function, which is computed as, SUM( INDEX (reference, row_num, [col_num]).
   We have already discussed how to compute the value of reference, row number, and column number in the above example. The selected reference table is shown below:
   
   Let’s compute the values directly on the formula bar.
3. Drag the mouse on the formula bar and compute the value inside the INDEX function. It will appear on the formula bar as: ‘=SUM(INDEX(C4:E5,0,E1)).’
   It will appear like the image shown below:
   
4. Press Enter. The sum of all the numbers in the table will appear on the specified cell, as shown below:
   

Relation between AVERAGE and INDEX function

Average is the common word that we have been studying since many years. Average is defined as the division of sum of all the values with the number of values. It is given by:

Average = Sum of all the values/ No. of values

In excel, we will directly declare AVERAGE function with the INDEX function to compute the average of the specified values.

Now, let’s consider the same table as of SUM. It is given below:

Here, we will find the AVERAGE of all the values given in the table. Consider the below steps:

1. Click on any cell outside the given table.
2. Now, we have to compute the INDEX function to find the resulted value, which is given as, AVERAGE( INDEX (reference, row_num, [col_num]). The selected reference table is shown below:
   
3. Drag the mouse on the formula bar and compute the value inside the INDEX function. It will appear on the formula bar as: ‘=AVERAGE(INDEX(C4:E5,0,E1)).’
   
4. Press Enter. The average of all the numbers in the table will appear on the specified cell, as shown below:
   

Important points

Points to remember before proceeding with INDEX formulas and examples

• The row_number denotes the rows in the array.
• The column_number denotes the columns in an array.
• If both the row_num and column_num are specified in the INDEX function, it returns the value of the intersection of the specified row and column.
• If the row_num and col_num does not point into the cell of an array, the INDEX function returns the #REF! Error.
• The row name and the column name should point to the specified array.
• Excel will return the entire row or column if the specified value in the function of row or column is 0.

Examples

Let’s discuss some examples for better understanding of the index function.

Example 1: To find the final price of lamp.

Consider the below table in excel.

We need first to reconsider the position of the lamp in the given table. The position of the final price column is two, and the lamp’s position is row three. The position is according to the selected reference data. Thus, the resulted value will arise from the intersection of the third row and the second column. Let’s implement with the help of INDEX function in excel.

The steps are as follows:

1. Click on the cell as given in the example.
2. Drag the mouse on the formula bar and compute the values of reference, row number, and column number in the INDEX function. Select the given data of the table by starting from the first element to the last element as the reference. It is shown below:
   
3. The formula will appear on the formula as: ‘=INDEX(C5:E11,3,2).’
4. Press Enter. The final price of lamp will appear on the specified cell, as shown below:
   

Example 2: To find the sum of the values in the third row and second column separately.

Consider the below table.

Method 1: To find the sum of the values present in the third row.

The third row in the table is shown below:

Consider the below steps:

1. Click on any cell outside the given table.
2. Now, we will compute the values in the formula ‘=SUM(INDEX(reference, row_num, col_num). The reference will be the table selected starting from the first element to the last element, as shown below:
   
3. Since we want to find the sum of the third row, we will compute the value of column as 0. 0 means considering all columns by default. Excel will count all the elements of the third row irrespective of the number of columns.
4. Drag the mouse on the formula bar to type the formula. It is given as:
   1. ‘=SUM(INDEX(C4:G10,3,0)).’ It will appear on the formula bar as:
      
5. Press Enter. The sum of the elements of the third row will appear on the specified cell, as shown below:
   

We can verify the sum using the summation symbol on the toolbar.

Method 2: To find the sum of values present in the second column.

Consider the below steps:

The second column in the table is shown below:

Consider the below steps:

1. Click on any cell outside the given table.
2. Now, we will compute the values in the formula ‘=SUM(INDEX(reference, row_num, col_num).
3. We will compute the row number and column number as (0,2). It means that excel will consider the elements of second column irrespective of the number of rows.
4. Drag the mouse on the formula bar to type the formula. It is given by:
   ‘=SUM(INDEX(C4:G10,0,2)).’ It will appear on the formula bar as:
   
5. Press Enter. The sum of the elements of the second column will appear on the specified cell, as shown below: