Blog

INDEX function in excel

The INDEX function in Excel is a versatile tool for retrieving specific values from a range of cells. It allows you to pinpoint a particular cell within a table or array based on its row and column indices. It is more powerful and flexible than other lookup functions as it gives option to retrieve data based on row and column numbers. Lets understand the Syntax of this INDEX excel function.

how to use INDEX function in excel

The INDEX function can be used in two ways:

1. Array Form:

The array form of the INDEX function returns a single value or an array of values from a specified range.

Syntax for Array Form :

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

• array: The range of cells containing the data.
• row_num: The row number in the array from which to retrieve the value.
• column_num: Optional. The column number in the array from which to retrieve the value. If omitted, INDEX returns the entire row.
• area_num: Optional. Used when the array contains multiple ranges. It specifies which range to use.

2. Reference Form:

The reference form of the INDEX function returns a reference to a specific cell within a range of cells.

Syntax for Reference Form :

=INDEX(array, row_num, [column_num])

The arguments are the same as the array form, except for the area parameter.

Example of INDEX Formula

INDEX example in simple way

In the below example we tried to use index function to fetch data sales data for Illinois state for the month of Apr-22. We will use simple reference form for this. As we can see we took array as C3:K25. Manually we can see the state name is 2nd column and Apr 22 is 4th row. By filling this info get the value as highlighted below.

INDEX example all rows for a column

In the below example we will try to use all rows for a column from the array. So we took the same reference form formula,just put row_num empty. It reflects all rows of Illinois as shown in the below image.

INDEX example all columns for a row

Now we will take one more example to understand this excel index function. In this example we are trying to fetch data for Mar 22. Mar 22 is the 3rd row in the array. So we selected array and row_num and left col_num as blank.By just doing this we will get all columns value against this row.

INDEX example with dynamic columns and rows

In the final example we will use excel INDEX MATCH to get values dynamically. It means INDEX MATCH will create a deadly combination where you don’t need to change column_num or row_num. MATCH function will automatically find position of rows and columns. Lets see how to use it.

In the above example you can see that we used array as used in the earlier examples. For row_num and col_num we used match function. So in this scenario if we change the names in cell V4 and V5 my excel index formula formula will find new position of these inputs.

Advantages and Disadvantages of Excel Index formula

The INDEX function in Excel offers several advantages over other lookup functions, such as VLOOKUP and HLOOKUP :

Advantages:

1. Flexibility: INDEX can perform both horizontal and vertical lookups, while VLOOKUP and HLOOKUP are limited to vertical and horizontal lookups, respectively.
2. Exact or Approximate Matches: INDEX can handle both exact and approximate matches, while VLOOKUP requires an exact match.
3. Multiple Criteria: INDEX can be combined with other functions, such as MATCH, to perform lookups based on multiple criteria.
4. Array Formula: INDEX can return an array of values, making it versatile for retrieving multiple values based on a single lookup value.
5. Efficiency: INDEX is generally faster than VLOOKUP and HLOOKUP for larger datasets.

However, INDEX also has some potential disadvantages:

Disadvantages:

1. Complexity: The INDEX function’s syntax can be more complex than VLOOKUP and HLOOKUP, especially when using multiple criteria or array formulas.
2. Less Familiar: INDEX is less commonly used than VLOOKUP and HLOOKUP, so there may be fewer resources available for learning and troubleshooting.
3. Compatibility: INDEX may not be as widely compatible with older versions of Excel.

You can read more on INDEX FUNCTION IN EXCEL from this link.

You can download this excel working file from below.

Conclusion:

Overall, the INDEX function provides a powerful and flexible tool for performing lookups in Excel, offering advantages in terms of flexibility, match options, and array capabilities. However, its slightly more complex syntax and less common usage may make it less accessible to some.