Confident Cells

INDEX and MATCH, one at a time: practice exercises

MATCH position, INDEX by number, two-way INDEX, MATCH bands. 4 exercises on real-looking data, each marked the moment you press Check. The first exercise is below to try now; the full lesson is part of Pro.

Your turn

Parking permits go to people in order from this waiting list. In H2, use MATCH to find the position on the list of the person named in H1.

ABCDEFGH
1Waiting listNameLiam
2ZoePosition
3Jordan
4Ollie
5Tanya
6Rachel
7Liam
8Ivan
9Tom
10Alex
11Jess
12Femi
13Kerry
14Lucy
15Hamza
16Nadia
17Hannah
18Nia
19Marek
20Will
21Sunil

How it works

MATCH(value, range, 0) answers "where is it?": the position of the value in the range, counting from 1. The 0 asks for an exact match.

INDEX(range, n) answers "what's at position n?". With a block of cells, INDEX(block, row, column) picks one cell. Put them together and you have a lookup that works in any direction.

=MATCH("Cara",A2:A6,0) =INDEX(A2:A6,4) =INDEX(B2:D6,2,3) =MATCH(B2,$F$2:$F$5,1)

Where is it, and what is there?

  • MATCH(value, range, 0) gives the position of the value in a row or column of cells. The 0 means "exact match only".
  • INDEX(range, n) gives the value at position n.

Positions count from the start of the range, not from row 1 of the sheet. In A2:A6, A2 is position 1.

Worked example

ABCD
1NameMonTueWed
2Amira352
3Ben416
4Cara723
5Dev284
6Ewa531
  1. =MATCH("Cara",A2:A6,0) in G1. Cara is the third name in A2:A6, so MATCH gives 3, even though she's on row 4 of the sheet.
  2. =INDEX(A2:A6,4) in G2. INDEX goes the other way: position 4 in A2:A6 is Dev.
  3. =INDEX(B2:D6,2,3) in G3. Give INDEX a block, a row number and a column number, and it picks one cell: row 2, column 3 of B2:D6 is Ben's Wednesday, 6.

MATCH for bands

With 1 instead of 0, MATCH finds the last value that is not more than the one you're looking for. The list must be in order, smallest first.

In a list of starting scores 0, 50, 70, 90, a score of 75 gives position 3: it has reached 70, but not 90.

What you'll practise

  1. Parking permits go to people in order from this waiting list. In H2, use MATCH to find the position on the list of the person named in H1.
  2. The on-call rota runs for 12 weeks. In H2, use INDEX to show who is on call in the week number typed in H1.
  3. The reception rota has a row for each week and a column for each day. In H3, use INDEX to show who covers reception in the week in H1 on the day number in H2 (1 for Monday).
  4. Levels 1 to 4 start at the scores in G2:G5, lowest first. In C2, use MATCH to find each person's level: the position of the last starting score they have reached.