## 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 formatThe 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,
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 formatThe 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,
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 functionWe 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: - Click on any cell outside the given table.
- 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.
- 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:
- Type the row number and the column number where the element to be found is present.
- 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.
- 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).'
- 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 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 functionAs 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:
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: - Click on any cell outside the given table.
- 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. - 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: - 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 functionAverage 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: - Click on any cell outside the given table.
- 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:
- 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)).'
- Press
**Enter**. The**average**of all the numbers in the table will appear on the specified cell, as shown below:
## Important pointsPoints 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.
## ExamplesLet'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: - Click on the cell as given in the example.
- 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:
- The formula will appear on the formula as: '=INDEX(C5:E11,3,2).'
- 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.
The third row in the table is shown below: Consider the below steps: - Click on any cell outside the given table.
- 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:
- 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.
- Drag the mouse on the formula bar to type the formula. It is given as:
- '=SUM(INDEX(C4:G10,3,0)).' It will appear on the formula bar as:
- '=SUM(INDEX(C4:G10,3,0)).' It will appear on the formula bar as:
- 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.
Consider the below steps: The second column in the table is shown below: Consider the below steps: - Click on any cell outside the given table.
- Now, we will compute the values in the formula '=SUM(INDEX(reference, row_num, col_num).
- 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.
- 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: - Press
**Enter**. The sum of the elements of the**second column**will appear on the specified cell, as shown below:
Next TopicHow to remove formula in Excel |