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, 20052014
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...
 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)
 Press Ctrl+C to copy the selected text.
 Press Esc to return to the Ready mode.
 Launch the Go To dialog by pressing the F5 key or
Ctrl+G.
 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.
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


