INDEX

What it does

INDEX picks one cell out of a range by its position: the 2nd row, or the 3rd row and 4th column. On its own it is handy for lists; paired with MATCH, which finds the position, it becomes a lookup that works in any direction.

Syntax

=INDEX(array, row_num, [column_num])

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

array

The range to pick from.

row_num

Which row of the range, counting from 1 at its top.

column_num

Which column of the range, counting from 1 at its left. Leave it out when the range is a single column.

Example

The results list has the runners in finishing order. Who came second?

The formula in D2:

=INDEX(B2:B4,2)

Result: Ben

The range B2:B4 holds Ana, Ben and Kai. Row 2 of that range is Ben, so the result is Ben.

Common mistakes

• Counting rows from the top of the sheet instead of from the top of the range. Row 1 is always the first row you selected.
• Asking for a row or column outside the range, which gives #REF!.
• Typing the position by hand when it should come from MATCH. A hard-coded number breaks as soon as the list changes order.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• INDEX: pick by positionModule 6 · INDEX and MATCH
• INDEX and MATCH togetherModule 6 · INDEX and MATCH
• XMATCHModule 6 · INDEX and MATCH
• A sales reportModule 14 · Combined skills

More from this module

• MATCH
• XMATCH

All functions