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)

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:

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:

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
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.
The first array will have index 1.
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.