Excel Index Function - Everything there is to know

The Excel INDEX function requires an array (or cell range) and a position. It returns the value that’s in the cell range at the given position.

Syntax

=INDEX(Array, Row number, [Column number], [Area number])

Argument		Argument description
1	Array	One or more arrays or cell ranges.
2	Row number	The row of the value we want to return.
3	Column number (Optional)	The column of the value we want to return.
4	Area number (Optional)	The array index of the value we want to return. Only useful when you have more than 1 array as the first argument.

Example 1: Barebones Index formula

In its simplest form, the Index function can look like this:

=INDEX(A1:A3, 2)

A barebones INDEX example. Given a cell range with values 1, 2 and 3, the value in the second row is 2.

Explanation

The result is 2 because we are finding the value in the second row in the given cell range (1, 2, 3).

Example 2: Columns

You can use the Column number argument for cell ranges with more than one column:

Getting the value at position (3, 2) of cell range A1:B3. 6 in this case.

Explanation

The returned value is 6 because we are looking in cell range A1:B3 at position row 3, column 2. And there is a 6 at that position.

About INDEX in combination with MATCH

The Index function is very often combined with the Match function because together they can be a versatile alternative to Vlookup. Since this requires some more explanation, we created a video and blog post about combining Match and Index.

Example 3: Area number

So we’ve seen the basics. Now it’s time to try out the more advanced stuff.

You can use the Index function with multiple arrays/cell ranges and choose not only the row and column of the value but also from which array to get the value. You do this using the Area number argument.

Here’s an example formula where we use two cell ranges (A1:B3 and D1:E3) and the Area number argument with value 2:

Using an Index function on two arrays. The last argument (Area number) is from which array we want to return a value. — Using an Index function on two arrays. The last argument (`Area number`) is from which array we want to return a value.

Explanation

The returned value is 12 because the Index function will return the value in the 3rd row of the 2nd column of the 2nd array, which is a 12 in this example.

The arrays are given with commas in between and enclosed in parentheses. You cannot use cell ranges that are from different worksheets or Excel will return a #VALUE error.

Questions

Can I hardcode an array to use with Index?

Yes, you can. To write this array, use curly braces ({, }) around the values and separate them using commas. Like this:
=INDEX({1, 2, 3, 4}, 2), which will return 2.

Does the array index for Area number start at 0 or at 1?

The first array will have index 1.

How to combine INDEX with MATCH?

You can learn how to combine these two functions in our video and blog post here.

Drawbacks

The Excel Index function has some drawbacks:

As mentioned earlier, you cannot use multiple input arrays that come from different worksheets. A #VALUE error will be returned if you do.