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:

### 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.