## How To Use Excel INDEX Function – Formulas And Exercise

**Excel INDEX Function**

The information returned by MATCH can be helpful when you use it with the INDEX function. INDEX returns the value found at a specified row and column intersection within a table. You can use MATCH to find the row and find the column and then use INDEX to get the actual data. The Excel INDEX function has two forms: an array form, which returns a value, and a reference form, which returns a cell reference. The forms of these functions are as follows:

=INDEX(array, row_num, column_num) |

=INDEX(reference, row_num, column_num, area_num) |

The array form works only with an array argument; it returns the value of the result, not

the cell reference. The result is the value at the position in array indicated by row_num and column_num. For example, the formula:

=INDEX({10,20,30;40,50,60} , 1, 2) |

returns the value 20, because 20 is the value in the cell in the second column and first row

of the array.

The reference form returns a cell address instead of a value and is useful when you want

to perform operations on a cell (such as changing the cell’s width) rather than on its value. This function can be confusing, however, because if an excel INDEX function is nested in another function, that function can use the value in the cell whose address is returned by INDEX. Furthermore, the reference form of INDEX doesn’t display its result as an address; it displays the value or values at that cell address. Remember that the result is an address, even if it doesn’t look like one.

Here are a few guidelines to keep in mind when using the Excel INDEX function:

● If you type 0 as the row_num or column_num argument, INDEX returns a reference

for the entire row or column, respectively.

● The reference argument can be one or more ranges, which are called areas. Each area

must be rectangular and can contain numbers, text, or formulas. If the areas are not

adjacent, you must enclose the reference argument in parentheses.

● You need the area_num argument only if you include more than one area in reference. The area_num argument identifies the area to which the row_num and column_ num arguments will be applied. The first area specified in reference is designated area 1, the second area 2, and so on.

Let’s consider some examples to see how all this works. The image below shows an example of an Excel INDEX function. The formula in cell A1 uses the row coordinate in cell A2 and the column coordinate in cell A3 to return the contents of the cell in the third row and second column of the specified range.

The following example is a bit trickier: Using the same worksheet as in the image above, the formula =INDEX(C3:E6, 0, 2) displays the #VALUE! error value because the row_num argument of 0 returns a reference to the entire column specified by the column_num argument of 2, or the range D3:D6. Excel can’t display a range as the result. However, try nesting this formula in another function as follows:

=SUM(INDEX(C3:E6, 0, 2)). |

The result is 2600, the sum of the values in D3:D6. This illustrates the utility of obtaining a reference as a result.

Now we’ll show how the Excel INDEX function works with multiple ranges in the reference argument. (When you’re using more than one range, you must enclose the argument in parentheses.) For example, in the formula:

=INDEX((A1:C5,D6:F10), 1, 1, 2), |

the reference range comprises two areas: A1:C5 and D6:F10. The area_num argument (2) tells INDEX to work on the second of these areas. This formula returns the address D6, which is the cell in the first column and first row of the range D6:F10. The displayed result is the value in that cell.

**Exercise: How to Use the Excel INDEX Function:**

**1.** **Click** a cell where you want the result to appear.

**2.** Enter **=INDEX(** to start the function.

**3.** Enter a **reference** to the table.

You can drag the mouse over the range or enter its address. If the table has been named, you can enter the name.

**4.** Enter a comma **(,)**.

**5.** Enter the row number relative to the table’s **first row**.

This number can be the result of a calculation or the value returned from a function.

**6.** Enter a comma **(,)**.

**7.** Enter the column number relative to the table’s leftmost column.

This number can be the result of a calculation or the value returned from a function.

**8.** Type a** ),** and press **Enter**.