For business users of Microsoft Excel Free guides and templates
Home >  Excel Help >  Excel Functions >  

Lookup and reference

INDEX Function

Uses an index value to choose a range from a reference or a value from an array.


by Charley Kyd, MBA
Microsoft Excel MVP, 2005-2014
The Father of Spreadsheet Dashboard Reports

(Download the workbooks.)

Managers...
Charley Kyd can personally help you to apply the Excel methods in this article to your own organization.

Click here to learn more.
Typically returns a cell reference at a particular row and column of a reference, or from all rows or columns of a reference. If the reference is made up of nonadjacent selections, you can pick the selection (the area) to look in.

Microsoft documentation describes two forms of the INDEX function: the reference form and the array form. These distinctions aren't necessary, however, as illustrated by Example 9.

Syntax

INDEX(reference, row_num, column_num, area_num)

  • reference  Required. A reference to one or more cell ranges.

    • If you are entering a nonadjacent selection for reference, enclose reference in parentheses. (See Example 4 below.)

    • If each area in reference contains only one row or column, the row_num or column_num argument, respectively, is optional. It can also be zero. For example, for a single row reference use any of these:

      • INDEX(reference,,column_num)
      • INDEX(reference,column_num)
      • INDEX(reference, 0, column_num)
    • The reference argument also can contain an array constant, as illustrated in by Example 9.
  • row_num  is the number of the row in reference from which to return a reference. If this value is zero or omitted, all rows in area are returned.

  • column_num  is the number of the column reference from which to return a reference. If this value is zero or omitted, all columns in area are returned.

  • area_num  selects a range in reference from which to return the intersection of row_num and column_num. The first area selected or entered is numbered 1, the second is 2, and so on. If area_num is omitted, INDEX uses area 1.

Remarks

The result of the INDEX is a reference that can be used by other functions in formulas.

Tip: If you're unsure what reference an INDEX function is returning, you can have Excel select the reference. To do so, follow these steps...

  1. Select the INDEX part of the formula in your formula bar. To illustrate, in Example 7 below, you would select: INDEX(Nuts,3,3):INDEX(Nuts,5,3)
  2. Press Ctrl+C to copy the selected text.
  3. Press Esc to return to the Ready mode.
  4. Launch the Go To dialog by pressing the F5 key or Ctrl+G.
  5. Press Ctrl+V to paste the text to the Reference box, then press OK. After you do so, Excel selects the reference returned by the INDEX function.

INDEX can return results from a closed external workbook.

Examples

You can download this example workbook here, along with all other example workbooks I've completed for this Excel help area.

In this worksheet, the range A3:C8 is named Fruit, and the range E3:G8 is named Nuts.

Examples for the INDEX function

Example 1:  =INDEX(Fruit,3,2) equals the reference B5, containing 0.89.

Example 2:  =INDEX(Nuts,1,3) equals the reference G3, which is blank. Excel therefore returns zero.

Example 3:  =INDEX(Nuts,2,4)  equals the #REF! error value, because the column_num argument (4) is out of range.

Example 4:  =INDEX((Fruit,Nuts),4,1,2) equals the reference E6, containing "Peanuts".

Example 5:  =INDEX(Nuts,0,3) equals the #VALUE! error, because the row_num argument of zero returns all rows, which means the formula tries to return the range G3:G8 to a single cell, and therefore generates an error value.

Example 6:  =SUM(INDEX(Nuts,0,3)) returns 100, which is the sum of the range G3:G8.

Example 7:  =SUM(INDEX(Nuts,3,3):INDEX(Nuts,5,3)) returns the value 90, which is the sum of the range G5 (returned by the first INDEX function) through G7 (returned by the second INDEX function).

Example 8: =SUM(INDEX(Fruit,,)) and =SUM(INDEX(Fruit,0,0)) both return the value 121.86, which is the sum of all numbers in the Fruit range. This is because if its row_num or column_num argument is omitted, or has the value of zero, INDEX returns the entire row or column. Therefore, when both arguments are omitted or have the value of zero, the entire reference is returned.

Example 9: =INDEX({30;20;50;15},2,1) and =INDEX({30;20;50;15},2,1,1) both return the value 20 from the array constant {30;20;50;15}. (Because semicolons separate the numbers, this array constant indicates a column of data. Using commas would indicate a row of data.) The array_num argument is optional here, just as it's optional when the reference argument specifies a single range, like Fruit or Nuts.

Applies To

Excel 2003 and above

Other ExcelUser Information

Other Help

 

Charley's SwipeFile charts


Free Excel Dashboards