The INDEX function returns a value or the reference to a value from within a table or range. There are two forms of the INDEX function: the array form and the reference form.

If you want to Then see
Return the value of a specified cell or array of cells Array form
Return a reference to specified cells Reference form

Array Form

Returns the value of an element in a table or an array, selected by the row and column number indexes. Use the array form if the first argument to INDEX is an array constant.

Syntax

=INDEX(array,row_num,[col_num])

Arguments

Argument Description
array The specified array or range of cells
row_num Defines the row number of the specified array. If set to zero or blank, this defaults to all rows in the supplied array
[col_num] Optional. Defines the column number of the specified array. If set to zero or blank, this defaults to all columns in the supplied array

Note: The row_num and the [col_num] arguments cannot both be zero or blank.

Array Form Examples

  A B C D E
1 OrderID Quantity Unit Cost Total Cost  
2 10567 2 $3.50 $7.00  
3 10568 4 $5.00 $20.00  
4 10569 3 $7.23 $21.69  
5 10570 12 $2.99 $35.88  
6          
7 Formula Result Notes
8 =INDEX(A1:D5,2,3) $3.50 Value found in the second row, third column in the range A1:D5
9 =INDEX(A1:D5,4,1) 10569 Value found in the fourth row, first column in the range A1:D5
10 =INDEX(A1:D5,5,2) 12 Value found in the fifth row, second column in the range A1:D5
11 =INDEX({1,2;3,4},1,2) 4 Value found in the third row, second column in the array. The array contains 1 and 2 in the first row and 3 and 4 in the second row

Note: The formulas in cell A11 is an array formula. For this formula to return values, press F2, and then press CTRL+Shift+Enter.

Reference Form

Returns the reference of the cell at the intersection of a particular row and column. If the reference is made up of nonadjacent selections, you can pick the selection to look in.

Syntax

=INDEX(reference,row_num,[col_num],[area_num])

Arguments

Argument Description
reference A reference to one or more cell ranges

  If you are entering a nonadjacent range for the reference, enclose reference in parentheses
  If each area in reference contains only one row or column, the row_num or [col_num] argument, respectively, is optional
row_num The number of the row in reference from which to return a reference
[col_num] Optional. The number of the column in reference from which to return a reference
[area_num] Optional. Selects a range in reference from which to return the intersection of row_num and [col_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

Note: Either, but not both, of the row_num or the [col_num] arguments may be zero or blank. If they are both zero or blank, the INDEX function will return an error.

Reference Form Examples

  A B C D E
1 OrderID Quantity Unit Cost Total Cost  
2 10567 2 $3.50 $7.00  
3 10568 4 $5.00 $20.00  
4 10569 3 $7.23 $21.69  
5 10570 12 $2.99 $35.88  
6          
7 Forumla Result Notes
8 =INDEX(A1:D5,2,3) $3.50 Returns a reference to cell C2 which contains the value $3.50
9 =INDEX((A1:B5,C1:D5),3,2) 4 Returns a reference to cell B3 which contains the value 4
10 =INDEX((A1:B5,C1:D5),3,2,2) $20.00 Returns a reference to cell D3 which contains the value $20.00

Common Function Error(s)

Problem What went wrong
#VALUE! Occurs if any of the supplied row_num[col_num] or [area_num] arguments are not numeric values
#REF! Occurs if either:

  the supplied row_num argument is greater than the number of rows in the supplied reference
  the supplied [col_num] argument is greater than the number of columns in the supplied reference
  the supplied [area_num] argument is greater than the number of areas in the supplied reference