Excel INDEX Function and Formula with Examples


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:

 ABCDEFG
1DateCodeNameQtyPrice ($)Amount ($)Seller
210/20/2019Item001Dell Laptop PC 0013225675James
310/20/2019Item002Dell Laptop PC 00227501500James
410/21/2019Item003Dell Laptop PC 0032235470John
510/21/2019Item001Dell Laptop PC 00137852355Roland
610/22/2019Item002Dell Laptop PC 002310503150Roland
710/22/2019Item003Dell Laptop PC 0031275275James
810/22/2019Item001Dell Laptop PC 0013290870John

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_numberThe column position in a given array

Remarks

  • If array contains only one row, row_number is optional.
  • If array contains only one column, column_number is optional.
  • If there is only one column in arraycolumn_number is optional.
  • If row_number is omitted, column_number is required.
  • If column_number is omitted, row_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_numberOptional. 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_numberarea_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 0INDEX() returns a reference to a range of cells of the entire column.
  • If you set column_number to 0INDEX() 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 ExcelINDEX( ) 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.

Leave a Reply

Your email address will not be published. Required fields are marked *