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

In this tutorial, you'll learn how to use the **INDEX function **in **Excel**.

**INDEX**()** **returns a value or a reference to a specified cell from within a range of cells.

You can use the **INDEX function** in two forms.

If you want to return to a value of a specified cell or array of cells, use the array form.

If you want to return a reference, use the reference form.

Demo data:

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**

The array form of **INDEX**() returns a value of a cell in an array or a table.

You can use the array form if the *first argument* is an array constant.

#### Array Form **Syntax**

The syntax of the array form is as follows:

=

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
*array*contains only one row,*row_number*is optional. - If
*array*contains only one column, c*olumn_number*is optional. - If there is only one column in
*array*,*column_number*is optional. - If
*row_number*is omitted, c*olumn_number*is required. - If
*column_number*is omitted, r*ow_number*is required. - If
*array*contains multiple rows and multiple columns, if you use only*row_number*or*column_number*,**INDEX**() returns an array of values for the entire rows or columns.

**Array Form Examples**

##### A reference containing multiple rows and columns

Example:

=

INDEX(A2:G8,7,7)

Both *row_number* and *column_number* are 7.

The above formula returns *Jonh, *which is the value of a cell at the intersection of the seventh row and the seventh column.

##### A reference containing a single column and multiple rows

Example:

=

INDEX(G2:G8,2)

You can omit *column_number* because there is only one column in a range of cells.

The *column_number* is 1. You can also write the above formula as follows:

=

INDEX(G2:G8,2,1)

The above formula returns *James*, which is the value of a cell at the intersection of the second row and the first column in the range.

##### A reference containing a single row and multiple columns

Example:

=

INDEX(A2:G2,3)

Since the range of cells contains only one row, you can omit the *row_number*.

The above returns *Dell Laptop PC 001*, which is the value of a cell at the intersection of the first row and the third column in the range.

##### An array containing multiple rows and columns

Example:

=

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

Here, (**{"Apple","Orange","Mango";"Melon","Pineapple","Banana"}**) is the array of two rows and three columns.

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

##### An array containing a single row and multiple columns

Example:

=

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

You can omit *row_number* because the array contains only one row.

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

##### An array containing a single column and multiple rows

Example:

=

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

Since the array contains only one column, you can omit *column_number*.

The above formula returns *Melon*, which is the value in the second row and first column in the array.

**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**

The syntax of the reference form is as follows:

=

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
*reference*contains multiple ranges,*area_number*indicates which range use. - If
*area_number*is omitted,**INDEX**() uses area 1. - If you set
*row_number*to**0**,**INDEX**() returns a reference to a range of cells of the entire column. - If you set
*column_number*to**0**,**INDEX**() returns a reference to a range of cells of the entire row.

**Reference Form Examples**

##### A reference containing a single row

Example:

=

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

The above formula returns *Dell Laptop PC 001*, which is the value of cell C2 (the intersection of the first row and the third column).

##### A reference containing a single column

Example:

=

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

The above formula returns *Dell Laptop PC 002*, which is the value of cell C3 (the intersection of the second row and the first column).

##### A reference containing multiple rows and columns

Example:

=

INDEX(A2:G8,2,7)

The above formula returns *James*, which is the value of cell D4 (the intersection of the 2nd row and the 7th column).

##### A reference containing multiple ranges

A reference can contain more than one range, as the following example shows:

=

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

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

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

##### A reference containing multiple ranges with a specified *area_number*

Example:

=

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

The formula returns *Dell Laptop PC 003*, which is the value of cell C7 at the intersection of the 2nd row and the 3rd column in the second area.

##### INDEX() and SUM()

You can use **INDEX**() and **SUM**() together, as the following example shows:

=

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

Here, *row_number* is set to 0, and *column_number* is set to 6.

**INDEX**() returns a** **reference to a range of cells F2 through F8.

**SUM()** sums all values in the range F2: F8 and returns 9295.

##### INDEX() and COUNT()

You can use **INDEX**() and **COUNT**() together, as the following example shows:

=

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

Here, *column_number* is set to 0, and the *row_number* is set to 3.

**INDEX**() returns a reference(B4:G4) to the entire third row in the range **B2: G8**.

**COUNT**() counts the number of cells in the range **B4: G4** and returns 3.

#### INDEX() and MATCH()

Refer to the following article to learn how to use **INDEX()** and **MATCH() ** together:

How to Use the INDEX and MATCH function.

In this tutorial, you've learned how to use the **INDEX function** in **Excel**. **INDEX**( ) returns a value or a reference to a value from within a range or table.

You can use the **INDEX function** in two ways, array form and reference form.

You can also use **INDEX**() with other functions, such as **SUM**(), **COUNT**(), and some other Excel functions.