# INDEX function in Excel

*The INDEX function in Excel is a very powerful formula within the search and reference functions. The INDEX function is considered the predecessor of VLOOKUP and HLOOKUP. This function has two forms of use: Array and Reference.*

**What is the INDEX function in Excel?**

The INDEX Function helps us find data or value within a range of cells or specified tables, allowing us to have the information at hand when it is required.

**What it does:**

- Returns the value at a given location within a specified range or array.
- Can extract single cells, entire rows, or columns of data.
- Can be used for lookups or to build dynamic formulas.

**How to Use the INDEX function in Excel?**

The INDEX Function searches within a table or range according to the row or column number we indicate (coordinates), a value or the reference of a value at the intersection of these.

There are two ways to use the INDEX Function, such as the Array Form and the Reference Form -

**Array Form of the INDEX Function**

The array form of the ** INDEX** function is especially useful when you want to extract multiple values from an array or range in a single formula. Instead of returning a single value, the

**function can return an array of results. This is possible by entering more than one row or column number as arguments.**

*array INDEX*The syntax of the *matrix* form is as follows:

**=INDEX(array; row_num; [column_num])**

The array form of the ** INDEX** function has the following arguments:

**Array (required):**The range of cells that comprise our data table.**row_num (optional):**The cell's row number containing the required value.**column_num (optional):**This argument is optional only if the Matrix consists of a single column. Otherwise, the column number of the searched cell must be specified.

**Example - Use of the Array Form of the INDEX Function**

Let's say we want to extract the sales figures for "Cycle" in June. We can achieve this using the INDEX function's array form. We will use the below formula -

**=INDEX(A2:N13,5,8)**

**Explanation**

- It extracts a value from a table (data_range) based on product and month.
- Imagine the table has product names as the first row and month names as the first column.
**product_row**(here, 5) tells the formula which row to look in for the product ("Cycle").**month_column**(here, 8) tells the formula which column to look in for the month (June).- The formula returns the value where those two meet (sales figure for "Cycle" in June).

**Reference Form of the INDEX Function**

The second way to use the *INDEX* function in Excel is similar to the previous one, but with the difference that instead of specifying a single matrix, we can indicate more than one matrix. This is useful when you only want to extract values from a specific part of your data.

The syntax of the *reference* form is as follows:

**=INDEX(ref, row_num, [column_num], [area_num])**

The reference form of the ** INDEX** function has the following arguments:

**Ref (required):**The reference to the arrays that hold the data. Ref can specify multiple ranges, such as A2:N7, A8:N13**row_num (required):**The row number of the cell we want to obtain.**column_num(optional):**The column number of the cell to obtain.**area_num (optional):**Since Ref indicates which ranges should be used, we must mention the reference range we pick. Usually denoted as 1 or 2.

**Example - Use of the Reference Form of the INDEX Function**

In this example, we want to extract the sales figures for "Cars" in March. We can achieve this using the INDEX function's reference form. We will use the below formula -

=INDEX((A2:N7,A8:N13),3,5,2)

**Explanation**

The formula uses a combined range (A2:N7,A8:N13). This means it's looking at two separate ranges: A2:N7 and A8:N13.

**row_num**(3): 3 specifies the row number but is now within the combined range.**column_num**(5): 5 specifies the column number within the chosen range (A8:N13 due to the tiebreaker).**area_num**: The third argument, 2, acts as a "tiebreaker" when using combined ranges. It tells the INDEX function to prioritise the second range (A8:N13) when there's a row match in both ranges at position 3.

It retrieves the value from the 3rd row and 5th column within the range A8:N13 (2). So, it essentially focuses on the second half of the data (rows 8-13). So after applying the formula, we get the result **130**, which shows that based on the given data, "Car" had sales of 130 units in March.

**Observations**

- If the row_num and column_num arguments are used, INDEX returns the cell's value at the intersection of the row_num and column_num arguments.
- The row_num and column_num arguments must indicate a cell within the array; otherwise, INDEX returns a #REF!
- If you set row_num or column_num to 0 (zero), INDEX returns the array of values for the entire column or row, respectively. Write the INDEX function as an array formula to use the returned values as an array.

**About the Author**

Rashmi is a postgraduate in Biotechnology with a flair for research-oriented work and has an experience of over 13 years in content creation and social media handling. She has a diversified writing portfolio and aim... Read Full Bio