Excel Index Function – Download Practice Sheet

Excel-Index-Function-Feature-Image--min

Excel Index Function:-

The Excel Index function is used to find out the value in a selected array or range by giving a position of the cell in the selected array. We will direct the excel to find out the value of cells on the particular position of the cell. This position can be provided dynamically by the match formula. I will explain it further in the next topics.

The feature of “Index” function: –

  • With the help of Index Function, we can retrieve the value of the cell in the selected array by providing the position of the cell.
  • It helps in getting a particular value of the certain position of the cell in the selected array.
  • We can provide position dynamically by the match function.

Explanation of Function:-

Now, We will explain the Arguments of the Function.

=INDEX(array, row_num, [col_num], [area_num])

array: –

An array is that area from which we will get the value of the cell by providing the position of the cell.

row_num: –

The row_num that selected row number or dynamically provided the row number by match function to the excel to get the value of the cell from the selected array.

col_num: –

The row_num that selected Column number or dynamically provided the column number by match function to the excel to get the value of the cell from the selected array.

area_num:-

When we have selected the two or more array then we will provide the number of the array.

Example of Function: – 

I will show you, how to apply the whole function. (using optional also).

From the following table get the value of the selected position of the cell: –

Excel Index Function Example min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Example–min

Solution:-

We will Apply the Match function and get the result. This is shown in the following steps: –

Step No. 1:-

We will write the “=Index( ” in the column of the result.

Excel Index Function Solution 1 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-1–min

Step No. 2

Now select the cells from which you want to get the value of the particular position.

We can select multiple arrays in single index formula. Like shown in the following:-

Excel Index Function Solution 2 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-2–min

Step No. 3

Now select the position of the value in the row.

Excel Index Function Solution 3 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-3–min

Step No. 4

Now select the position of the value in the Column. If multiple columns selected then we need it rather then its default value is equal to 1.

Excel Index Function Solution 4 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-4–min

Step No. 5

Now select the number of the array, if we have selected multiple arrays like shown in the example.

Excel Index Function Solution 5 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-5–min

After all these steps we will get the result shown as follow: –

Excel Index Function Solution 6 min - Excel Index Function - Download Practice Sheet
Excel-Index-Function-Solution-6–min

Thanks 

Please Share and comment your feedback.

to buy the Microsoft Excel Click Here.