INDEX: picking a cell by position

1 / 5

The runners are in finishing order. Find who came in each place in E2:E6.

keyboard Typing formulas is easiest on a computer with a keyboard.

1st to 10th

names go here

Type to enter a value or a formula starting with =. F2 edits, F4 adds $ to a reference.

Study notes

How INDEX works

What does INDEX do?

INDEX is the opposite of MATCH. You give it a range and a position, and it gives back the value at that position.
=INDEX(array, row_num)
=INDEX($A$2:$A$11,3) counts 3 cells down from A2 and returns what is in A4.
How are positions counted?

From the first cell of the range, which is position 1. Start the range at the header and the header becomes position 1, so every answer is one place too early.
What about tables with several columns?

Add a third part, the column: =INDEX($A$2:$B$11,3,2) goes 3 rows down and 2 columns across, giving B4.
Where does the position come from?

Here you type it, or it sits in a cell. Usually it comes from MATCH, which finds the position of a value. Put the two together and you have a lookup that can return any column, even one to the left.

Know someone fighting with a spreadsheet? Send them this page.