INDEX Excel Function

What is the Index Function in Excel?

Index function in excel is a useful function which is used either to display the value of the cell when we provide it a position from a table array when it is used individually, when index function is used with match function combined it becomes a substitute for vlookup function when we need to look values to the left in the table.

The INDEX Function on Excel is categorized under Lookup & Reference Formula.

Index Function

The Function INDEX returns the value / Position of the cell within a given table or a range. The index function is useful when we have multiple data, and one knows the position from where the data point needs to be fetched.

The Index function can also be used as a replacement for VLOOKUP when the data you want to retrieve is to the left side of the lookup column

INDEX function can be used in 2 different uses:

1) lookup a value that is at the intersection of a row and column.

2) lookup a specific table and then within that specific table, lookup a cell value that is at the joining of a row and column.

INDEX Formula in Excel

  • Array Form

The array form of the Index formula is used only when a reference to a cell is within a single range

INDEX Formula in Excel

Parameters of INDEX Formula in Excel

  • Array: Array is defined as the specific range of cells
  • row_num: It denotes the position of the row in the specified array.
  • [column_num]: It denotes the position of the column in the specified array.

Note: Either row_num / column number is mandatory; it will give #VALUE! Error if both the value is blank / zero.

How to Use the INDEX Function in Excel

The INDEX function is very simple and easy to use. Let us understand the working of INDEX in Excel by some examples.

You can download this INDEX Function Excel Template here – INDEX Function Excel Template

Example #1

INDEX Function Example 1

Result:

INDEX Function Example 1 1 1

In the above example, the Index function has only one single range, and it returns a position to row 5 of the range C3:C7, which is cell C7. This has the value 4

Example #2

INDEX Function Example 2

Result:

INDEX Function Example 2-1

In the above example, Index will return the cell reference to column number 4 and row number 3 of the range B3:F7, which is cell E5. This has the value of 629

INDEX Function Example 2-2

The example will return  #VALUE if both row no, column no is zero.

  • Reference Form

=INDEX(reference, row_num, [column_num], [area_num])

The reference format of Index is used only when a reference to a cell is within a multiple ranges

  1. Array: Array is defined as the specific range of cells / Range. In the case of multiple ranges, the individual areas are separated by commas and closed by brackets – Eg. ( A1:C2, C4:D7).
  2. row_num: It denotes the position of the row in the specified array.
  3. [column_num]: It denotes the position of the column in the specified array.
  4. Area_num: Area number picks a range in reference from which returns the intersection of Column_num and Row_num.

Note: If the Area_num is left blank then, the INDEX Function on Excel uses area 1 as default

The Index Function returns #VALUE! Error if the area which is mentioned in the INDEX Formula in excel is in any other sheet. Areas mentioned in INDEX Formula excel must be located on one sheet.

For Example:

Example 3

Example #3

Example 3-1

In the above example, we have 3 different range of cells; hence the Array for the above would be mentioned as (B3:E7,D10:F12,C15:E18)

Result:

Example 3-2

In the above example, the Index function returns the reference to column number 4 and row number 3 of the second area {D10:F12}, which refers to cell E11.

This has the value of 665

Things to be Remember

  • If the column number or the row number is 0 (Zero), it will return the complete values of the specific row or column, respectively.
  • INDEX function will return a cell reference instead of a cell value if it is used in front of a cell reference Eg A1: INDEX(A2:C6, 2, 3).
  • INDEX Function is extensively used with MATCH function in Excel.
  • Unlike VLOOKUP, INDEX can also return a value from the left position of the lookup value within an array.
  • All the parameters used in the INDEX formula in excel, such as Row_num, Column_num, and Area_num, should refer to a cell within the array defined; otherwise, the INDEX function on Excel will return #REF! error value.
  • If Row_num or Column_num is blank or zero, this will default to all the rows or Columns in the mentioned array.

Recommended Articles

This has been a guide to the INDEX Function in Excel. Here we discuss the INDEX Formula in excel and how to use the INDEX Excel function along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Reader Interactions

Leave a Reply

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