The INDEX guide
=INDEX($A$4:$A$13, D4) returns the cell at position D4 in A4:A13: 1 is A4, 3 is A6, 10 is A13. INDEX counts from the first cell of the range you give it.
What INDEX does
Give INDEX a range and a number, and it returns the cell in that position. On a list sorted best first, the position is the place: =INDEX(A4:A13, 3) is whoever came third. It's the opposite of MATCH, which takes a value and gives its position.
Syntax
=INDEX(array, row_num, [column_num])
- array: the range. For a single column, the row number is all INDEX needs.
- row_num: the position, counted from 1 at the top of the range.
- column_num: for a range with several columns, which column.
INDEX(A4:B13, 3, 2)is the sales of the third rep.
It counts from the range, not the sheet
The leaderboard has a title in A1, a note in A2 and headers in row 3, so the winner sits in row 4. =INDEX(A4:A13, 1) is the winner. =INDEX(A1:A13, 1) is the title, and every place after it is three rows too high. Start the range at the first value.
Positions outside the range
A position larger than the range gives #REF!: a list of 9 has no 10th place. A row number of 0 means the whole column: in Excel 365, =INDEX(A4:A13, 0) spills all ten names into the cells below.
Common mistakes
- A range that starts at the title or the header. Every answer comes from too high up.
- Counting from 0. Place 1 is the first cell. Subtracting 1 shifts every answer up a row.
- Not locking the range. Filled down, A4:A13 becomes A5:A14 and the winner drops out. Use
$A$4:$A$13. - A range that stops early. The last places give #REF!.
Next: INDEX with MATCH to look up in any direction, then a two-way lookup.
FAQ
- What does the INDEX function do in Excel?
- It returns the value at a given position in a range. =INDEX(A4:A13,3) returns the third cell of A4:A13, which is A6.
- Does INDEX count from the top of the sheet?
- No, from the top of the range you give it. In A4:A13, position 1 is A4.
- Why does INDEX return #REF!?
- The position is outside the range: larger than the number of cells, or a column number the range doesn't have.
- What does INDEX with 0 do?
- A row number of 0 returns the whole column of the range. In Excel 365 it spills into the cells below.



































































