## How to Use the INDEX Function and Formula in Excel

In this tutorial, you'll learn how to use the **Excel INDEX function**. **INDEX **is a function to get the value of a cell or reference to a cell value from a range of cells or table based on the location of a given cell.

There are two ways to use the **Excel INDEX function**, Array form, and reference form.

The following is the sample list of items that we're going to use in the examples:

A | B | C | D | E | F | G | |

1 | Date | Code | Name | Qty | Price ($) | Amount ($) | Seller |

2 | 10/20/2019 | Item001 | Dell Laptop PC 001 | 3 | 225 | 675 | James |

3 | 10/20/2019 | Item002 | Dell Laptop PC 002 | 2 | 750 | 1500 | James |

4 | 10/21/2019 | Item003 | Dell Laptop PC 003 | 2 | 235 | 470 | John |

5 | 10/21/2019 | Item001 | Dell Laptop PC 001 | 3 | 785 | 2355 | Roland |

6 | 10/22/2019 | Item002 | Dell Laptop PC 002 | 3 | 1050 | 3150 | Roland |

7 | 10/22/2019 | Item003 | Dell Laptop PC 003 | 1 | 275 | 275 | James |

8 | 10/22/2019 | Item001 | Dell Laptop PC 001 | 3 | 290 | 870 | John |

**Array form**

**Array form** returns the value of a cell in an array or a table, selected by the row and column number indexes. You use the array form if the **first argument** is an array constant.

#### Array Form **Syntax**

=

INDEX(array,row_number, [column_number])

**Return Value**

The value of a cell.

**Arguments**

**array**: The range of cells or an array constant.**row_number:**The row position in a given array.**column_number:**The column position in a given array.

#### Remarks

- If the
**array**contains only one row,**row_number**is optional. - If
**row_number**omitted,**column_number**is required. - If the
**array**contains only one column,**column_number**is optional. - If
**column_number**omitted,**row_number**is required. - If an
**array**consists of more than one row and one column, and only**row_number**or**column_number**used,**INDEX**returns the array of the entire row or column. - If
**row_number**is 0,**INDEX**returns an array of values for the entire row. - If
**column_number**is 0,**INDEX**returns an array of values for the entire column.

**Array Form Examples**

##### Example 1: Both row_number and column_number Used

Let's take a look at an example:

=

INDEX(A2:G8,7,7)

In the above formula, both **row_number** and **column_number** used. Here, the **row_number** is 7, and **column_number** is 7, so INDEX returns the value of the cell at the intersection of the seventh row and seventh column in the range A2:G8. The result is **Jonh**.

##### Example 2: column_number Omitted

Now, let's take a look at the following INDEX formula:

=

INDEX(G2:G8,2)

As you can see, there are only two arguments, and the third argument omitted. Since the array contains only one column, the **column_number** omitted.

The **column_number** is 1, so you also can write the above formula as follows:

=

INDEX(G2:G8,2,1)

**INDEX** returns **James**, which is the value at the intersection of the second row and first column in the range G2:G8.

##### Example 3: row_number Omitted

Let's take a look at an example that **row_number** omitted:

=

INDEX(A2:G2,3)

As you can see, there are only two arguments, and the third omitted. The range A2:G2 contains only one row, so the **row_number** omitted.

**INDEX** returns **Dell Laptop PC 001**, which is the value at the intersection of the first row and third column in the range A2:G2.

##### Example 4: Using an Array and Both row_number and column_number Used

Let's look at the INDEX formula below.

=

INDEX({"Apple","Orange","Mango";"Melon","Pineapple","Banana"},1,3)

Here, we use an array in the INDEX function. The first argument (**{"Apple","Orange","Mango";"Melon","Pineapple","Banana"}**) is the array of two rows and three columns, and the second argument (**row_number**) is 1, and the third argument (**column_number**) is 3.

The above formula returns **Mango**, which is the value at the first row and third column in the array.

##### Example 5: Using an Array and row_number Omitted

The following example illustrates how to use an array with **row_number** omitted:

=

INDEX({"Apple","Melon","Orange"},3)

Here, the **row_number** omitted because the array contains only one row.

**INDEX** returns **Orange**, which is the value in the first row and third column in the array **{"Apple","Melon","Orange"}**.

##### Example 6: Using an Array and column_number Omitted

The following **INDEX** formula illustrates how to use an array with **column_number** omitted:

=

INDEX({"Apple";"Melon";"Orange"},2)

Here, **column_number** omitted because the array contains only one column.

**INDEX** returns **Melon**, which is the value in the second row and first column in the array **{"Apple";"Melon";"Orange"}**.

**Reference Form**

The reference form of the INDEX function returns the reference of the cell at the intersection of ** row_number** and

**.**

*column_number***Syntax**

=

INDEX(reference,row_number, [column_number], [area_number])

**Return Value**

The value of a cell.

**Arguments**

**reference**: The reference to one or more cell ranges.**row_number:**The number of the row in reference from which to return a reference.**column_number:**Optional. The number of the column in reference from which to return a reference.**area_number**: Optional. The range in reference from which to return the intersection of**column_number**and**row_number**.**area_number**supplied as a number.

#### Remarks

- If there are multiple ranges,
**area_number**indicates to which range use. - If
**area_number**is omitted,**INDEX**uses area 1. - If
**row_number**is**0**,**INDEX**returns the reference to the entire column. - If
**column_number**is**0**,**INDEX**returns the reference to the entire row.

**Reference Form Examples**

##### Example 7: The Reference Containing a Single Row

The following example illustrates the INDEX formula, which the reference contains only one row:

=

INDEX(A2:G2,3) or =INDEX(A2:G2,1,3)

Since the reference A2: G2 contains only one row, you can omit **row_number**.

**INDEX** returns **Dell Laptop PC 001**, which is the content of cell **C2 **(the intersection of the first row and third column).

##### Example 8: The Reference Containing a Single Column

The following example illustrates the INDEX formula, which the reference contains only one column:

=

INDEX(C2:C8,2)or =INDEX(D2:D8,2,1)

Here, you can omit **column_number** because the reference A2:G2 contains only one column.

**INDEX** returns **Dell Laptop PC 002**, which is the content of cell **C3 **(the intersection of the second row and first column).

##### Example 9: Using Both column_number and row_number

The following illustrates an example of using both **row_number** and **column_number**:

=

INDEX(A2:G8,2,7)

The above formula returns **James**, which is the contents of cell D4 (the intersection of the 2nd row and 7th column in a range of cell A2 throw G8).

##### Example 10: Using Multiple Ranges

The following illustrates an example of using two ranges:

=

INDEX((A2:G3,A6:G8),2,3)

As you can see, the reference contains two ranges, **A2: G3** and** A6: G8**,** **where **A2: G3** is the reference to the 1st selected area, **A6: G8** is the reference to the 2nd selected area, **row_number** is 2, and **column_number** is 3.

**INDEX** returns **Dell Laptop PC 002, **which is an intersection of the second row and third column in the first selected area (the content of cell C3).

*Note that area_number omitted, so INDEX uses area 1. *

##### Example 11: Multiple Ranges and **area_number**

The following example illustrates a formula that contains two ranges and uses area_number:

=

INDEX((A2:G3,A6:G8),2,3,2)

Here, the reference containing two ranges A2:G3 and A6:G8, where A2:G3 is the reference to the 1st selected area, A6:G8 is the reference to the 2nd selected area, **row_number** 2, **column_number** 3, and **area_number** 2.

**INDEX **returns **Dell Laptop PC 003**, which an intersection of the 2nd row and 3rd column in the second area (A6:G8).

##### Example 12: Using INDEX Inside the SUM Function

The following illustrates how to use INDEX inside the SUM function:

=

SUM(INDEX(A2:G8,0,6))

Here, **row_number** is 0, and **column_number** is 6. **INDEX** returns the** reference** to the entire **sixth column** in the range A2: G8. The **reference** returned from the **INDEX** function, is a range of cells **F2** through **F8**. **SUM** sums the values in the range **F2:F8** and returns 9295.

##### Example 13: Using INDEX Inside the COUNT Function

The following illustrates how to use INDEX inside the COUNT function:

=

COUNT(INDEX(B2:G8,3,0))

Here, **column_number** is 0, and the row_number is 3, so **INDEX** returns the **reference** to the entire **third row** in the range **B2:G8**. The return **reference** from the INDEX function is a range of cells **B4** through **G4**. **COUNT** counts the number of cells in the range **B4:G4** and returns 3.

#### Example 14: Using INDEX and MATCH

Refer to the following article to learn how to use the INDEX and MATCH functions together:

**How to Use the INDEX and MATCH function**.

In this tutorial, you've learned how to use the **INDEX function** in Excel. **INDEX** is a function to retrieve the value of cell or reference to a cell from a range or table based on the location of a given cell. There are two ways to use the **Excel INDEX function,** array form, and reference form. You also can use **INDEX** inside other functions such as SUM, COUNT, and some other Excel functions.