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 Excel INDEX functionINDEX 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 functionArray form, and reference form.

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

 ABCDEFG
1DateCodeNameQtyPrice ($)Amount ($)Seller
210/20/2019Item001Dell Laptop PC 0013225675James
310/20/2019Item002Dell Laptop PC 00227501500James
410/21/2019Item003 Dell 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

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_numberarea_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 F8SUM 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 G4COUNT 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.

Leave a Reply

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