INDEX

About this lesson

INDEX returns the value at a position inside a range. The third cell down a column, or the cell in row 2, column 3 of a block.

By the end you can

fetch the value at a position in a column with INDEX.
fetch a cell from a block by its row and column inside the block.
say why INDEX(A1:D4,2,2) and INDEX(B2:D4,2,2) are different cells.

Practises INDEX MATCH XLOOKUP VLOOKUP

By Ali, Founder, Excel Lessons

The idea

On its own it looks useless, because you must already know the position. That is the idea: it is one half of a pair. INDEX turns a position into a value. MATCH turns a value into a position. Together they do everything VLOOKUP does, without its two limits.

Learn INDEX by itself first, because the counting rule catches people later. The numbers are positions inside the range you gave it, not row and column numbers on the sheet. Give it B2:D4 and position 1,1 is B2, not A1.

The mistake to watch for

A block that starts one row or column away from the data. INDEX counts positions inside the range you gave it, not on the sheet. So a block that starts one row early shifts every answer by one, with no error. When INDEX returns the cell next to the one you wanted, check where the block starts.

Where this comes up again

XLOOKUP: Looking Left, Not Found and Next Larger Advanced Lookups
Two-Way Lookup: INDEX with Two MATCHes Advanced Lookups
Choosing a Lookup: Putting It Together Advanced Lookups
Approximate Match: Bands and Brackets Advanced Lookups

This lesson needs JavaScript to run. Everything below is the lesson in full, but you cannot type into the grid or be marked.

Type a formula

INDEX returns whatever sits at a position inside a range. Given a single column, it needs one number: how far down.

In `F2`, type:

=INDEX(B2:B4,3)

B2:B4 is Monday's column, 3 the third cell down.

Row	A	B	C	D
1	Van	Mon	Tue	Wed
2	Van 1	Ola	Ola	Sam
3	Van 2	Jo	Ravi	Ravi
4	Van 3	Sam	Jo	Ola
5
6
7
8

Every step

1

INDEX returns whatever sits at a position inside a range. Given a single column, it needs one number: how far down. In F2, type: =INDEX(B2:B4,3). B2:B4 is Monday's column, 3 the third cell down.

Hint. B2:B4, then 3.
2

Read =INDEX(C2:C4,2) and say who it returns. That is Tuesday's column, second cell down.

Hint. Second cell of C2:C4.
3

For a block, INDEX needs two numbers: how far down, then how far across. Both count from the block's top-left cell. In F3, find who is on Van 2 on Tuesday. Shape: =INDEX(range,row,col). Use the block B2:D4: row 2, column 2.

Hint. B2:D4, 2, 2.
4

In F4, find who is on Van 1 on Wednesday, from the same block.

Hint. Row 1, column 3.
5

A range one row tall works like a column turned sideways: the one number counts across. In F5, find the third name along Van 3's row, B4:D4.

Hint. B4:D4, then 3.
6

The trap: INDEX counts inside the range, not on the sheet. Read =INDEX(A1:D4,2,2) and say who it returns. The block now starts at A1, one row up and one column left.

Hint. Start counting at A1.