**Formula**

**=INDEX(array,row_number,column_number)**

**Explanation**

The Excel INDEX function can be used to return a value or item at a certain position within a range. The Excel INDEX function can as well be used to get individual values or that of the entire columns and rows within a table. Most times, INDEX is used hand-in-hand with the MATCH function, and in this case, the MATCH function locates and provides a location to the INDEX function.

**Purpose **

Used in getting a value within a list or table based on its location

**Return Value **

The exact value matching the given location along the row and down the column.

**Logical Arguments **

**Array**: This is a range of cells within the table or an array constant.**Row Number**: This is the row number the exact value sort for appears in the array.**Column Number**: This is the column number the exact value sort for appears in the array (optional).**Area Number:**This is the reference to the range that will be used (optional)

**Example 1**

The example below uses the formula:

`=INDEX(B6:E14,8,3)`

And this will return the value in cell D13.

*Figure 1: Example of how to use the Excel INDEX Function to get the population of Sweden using the array format*

**Usage Notes for the Excel INDEX function**

To get a value from a list or table based on its location, the Excel INDEX function can be used.

**Array and Reference are the two forms of the Excel INDEX function.**

**Array Form of Excel INDEX Function**

Here, the first factor is an **array** and this is given as a range of cells or an array constant. The formula for the array form of INDEX is given below:

**=INDEX(array,rownumber,[columnnumber])**

** Reference Form**

In the reference form of INDEX, **reference is **the first factor and this is provided as a reference to one cell range or more cells or arrays within the table.

Then the formula for the reference form of INDEX function is:

**=INDEX(reference,rownumber,[columnnumber],[areanumber])**

Here, the reference form of Excel INDEX function displays the reference of the cell at the point of intersection between the **row number **and **column number**.

Area number will indicate which range to be used when the **reference** is presented as multiple arrays within a table.

Area sum is usually provided as a figure. For instance, if we have a formula as =INDEX((B1:D5,B7:E10),2,2,2). The area number in this formula will be 2 which is a representation of the range between B7:E10.

**Note**

- The Excel INDEX function will display the value at the point of intersection of both the col_num and row_num and when the row_num and col_num are provided
- The Excel INDEX function will display a range of values for the entire row.
**The Excel INDEX function will display a range of values for the complete column when the column number is zero.**

## Leave a Comment