INDEX function

Category: Lookup and reference functions

What does this function do?

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.

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, [column_num])

• Array (required):  A range of cells or an array constant. If array contains only one row or column, the corresponding Row_num or Column_num argument is optional. If array has more than one row and more than one column, and only Row_num or Column_num is used, INDEX returns an array of the entire row or column in array.

• Row_num (required): Selects the row in array from which to return a value. If Row_num is omitted, Column_num is required.

• Column_num (optional): Selects the column in array from which to return a value. If Column_num is omitted, Row_num is required.

Examples:

Let's say we have a range like the following (A1:B5):

A	B
Product	Count
A	25
B	38
C	40
D	41

=INDEX(A1:B5,3,1)= B
=INDEX(A1:B5,1,1)= Product

Reference Form

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

Syntax:

INDEX(reference, row_num, [column_num], [area_num])

• Reference (required): A reference to one or more cell ranges. If you are entering a non-adjacent range for the reference, enclose reference in parentheses. If each area in reference contains only one row or column, the Row_num or Column_num argument, respectively, is optional. For example, for a single row reference, use INDEX(reference,,column_num).

• Row_num (required): The number of the row in reference from which to return a reference.

• Column_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 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.

Examples:

Let's say we have the same range as before in (A1:B5):

A	B
Product	Count
A	25
B	38
C	40
D	41

=INDEX(A1:B5,3,1)= B
=INDEX((A1:B2,A3:B5),1,2,2)= 38, as there 2 references {A1:B2} & {A3:B5} and the Area_num indicates that we will index from the 2nd one.