How to use INDEX Function in Excel?

Hello all, Today we will discuss INDEX Function of Excel.It’s a very useful Function that helps in finding out a specific value from a given range of cells or from an array.Index Function returns the value from the table of a given element specified by row number and column number.



A syntax of INDEX Function:

                                    =INDEX( array/reference, row_number, column_number )


  • array/reference: It is a mandatory field,One can choose any of the two formats either array format or reference format to define how you want your returned value,If you want to return the value in the form of a specified cell or array of cells,then use array format otherwise If you want to return a reference to specified cells,then use reference format.
  • row_number: It is also a required field, It defines the row number of the array or in reference array.If it is zero, then all rows in the supplied array would be considered.
  • column_number: It becomes an optional field if row_number is specified, otherwise it is mandatory.

Now, here you might be confused with array and reference formatting, So let’s discuss it in detail.

  • Talking about array format, the very first parameter is the array or a group of cells, syntax for which can be defined as:

                              =INDEX (array, row_num, col_num)

  • In the reference format, the first parameter is the reference, which is supplied as a reference to one or more cell ranges. The syntax for the reference form of INDEX is:

                            =INDEX (reference, row_num, col_num,area_num)

NOTE: area_num, here defines the area number, as in reference formatting, there are more than one set of arrays that are recognized by area number.

Let’s see how we would write INDEX Function in array and reference format,


Looking into the example below, you would notice that the Index function is defining the row 5 and column 2 for the lookup value, which has the value 3 as doodled in the screenshot.

 =INDEX (C1:D5,5,2)

Example of use of the Excel Index function             Excel Index Function Result


In the following example, the Index function returns a reference to row 4 and column 2 of the 1st area in the supplied range. This is cell D4, which evaluates the value 5.


Here, you will notice the difference, there are three areas C1:D5, B7:C8 and B10:D12 and we have specified area ‘1’ in our INDEX formula, So, the result so obtained is from area 1 that is 5.

Example of use of the Excel Index function       Excel Index Function Result

So, this is How you can use INDEX Function in two ways, array form and reference form.We hope you understood the concept of INDEX Function.If you think this article was helpful to you, kindly share it with your friends and colleagues who are dealing with problems in understanding Excel Concepts.You can share it on Facebook, Twitter, Instagram or any other social platforms and can help others as well.You can also write your queries to us, we would try our best to help you as soon as possible.

Add a Comment

Your email address will not be published. Required fields are marked *