Index Function

     Today we will learn about Index Function in Excel. Index Function in Excel is one of the most useful function. When we combine this function with another function like Match etc, it will produce the dynamic result.

The INDEX function returns a value or the reference to a value from within a table or range.

In the below image we can see the Index function has two different formula like :-

1.   Index (Array, Row_num,[Col_num]

2. Index (Reference, Row_num,[col_num],[area_num]

In the first formula it is Array and in the second function in place array it is Reference.

We will give separate example for both of the format.

Array form

We can see that some values are in the bracket like [column_num] and in second function [column_num] and [area_num].  i.e. if these values does not exist then we will consider these values as "0" and the function will produce the result by without these values.

Image 1

We will understand the use of Index Function in Excel by following example

Example 1.

Image 2

In the above image when we put the Index Formula such as:- = index(A2:E10,5,4)

In this example we can see the Index Function parameters like (Array, row_num, col_num)

Where  Array is                (A2:E10),

Row_num      is                     5

Col_num        is                     4

Simply the pure explanation is we want the result from area A2:E10, where Row_num is "5" and Col_num "4" and simply answer is "13". We can see red circled, the above image is the intersection of the Row_num and Col_num.

Example 2.

Image 3

In this example we can see the range is (A2:C11) where row_num is "6" and column_num is "3"

Result is from range (A2:C11),row_num(6),column_num(3)= "1943"

In range (A2:C11) intersection of row_num=6, and column_num=3 is "1943"

Example 3.

In the below given example, we can see that row_num and col_num  has the cell reference like:- "D2=Row_num" and "D3=Col_num"

Image 4

In this example if we change the "row_num" and "col_num" according to need then we do not need to change the formula, it will produce the result automatically, "Just try it" because in this example "row_num" and "col_num" are cell reference. So we can change the value.

Reference form

In the second part of Index Function, the first parameter is reference, which is supplied as a reference to one or more cell range. The syntax of reference is :

Index (reference, Row_num.[col_num],[area_num]

Where [col_num] and [area_num] is optional

See the following example to know it better :

Image 5

=INDEX((A2:C5,A7:C10),2,2,1)

In this image we can see that the whole range is divided in two section such as "A2:C5" and "A7:C10"  then come to "row_num" (2) and column_num(2) the and the last section is "area_num"

which is the important in this example. "Area_num" is telling to us that the figure we are looking for, is in which area, whether it is area_num =1 ("A2:C5")  or area_num =2 ("A7:C10")

In the above example if we put "2" in the area_num the result would be "Vaucheron & Constantin 18K" but if we put "1" in the area_num then result would be different i. e. "Cartier Tank 18K"

In our further tutorial we will learn to combine the Index Function with the other function like "Match", "Count" and will see that what kind of the result we will get.

Regards

NarendrasExcelTips.blogspot.com