Excel Practice
Lessons Lesson 40

MATCH

About this lesson

MATCH searches a range for a value and returns its position in that range, as a number.

By the end you can

  • find where a value sits in a list with MATCH.
  • read the value to find from a cell rather than typing it.
  • say why the range you give MATCH changes the number it returns.

Practises MATCH INDEX XLOOKUP VLOOKUP

The idea

It answers the question INDEX needs to ask. The third argument is the match type. It should almost always be 0, which means exact. Leave it off and MATCH assumes the range is sorted. It returns the position of the largest value that is still below what you asked for. On an unsorted list that is a number that means nothing.

The other trap is quieter. The position is counted from the start of the range you gave. Include a heading row and every answer moves by one. That is fine, as long as the INDEX that uses the number was given the same range.

The mistake to watch for

Leaving off the third argument. Without the 0, MATCH assumes the range is sorted. It returns the position of the largest value still below what you asked for. On an unsorted list, that number means nothing. Write the 0. And give INDEX a range that starts on the same row.

Where this comes up again

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

MATCH searches a range for a value and returns its position as a number.

In E2, type =MATCH("Cara",A2:A6,0).

Cara is what to find. A2:A6 is the runners. 0 means the match must be exact.

E2
Row A B C D E F G
1 Runner Time
2 Adeola 41 Elsa
3 Bruno 38
4 Cara 45
5 Dimitri 36
6 Elsa 43
7
8

Every step

  1. 1

    MATCH searches a range for a value and returns its position as a number. In E2, type =MATCH("Cara",A2:A6,0). Cara is what to find. A2:A6 is the runners. 0 means the match must be exact.

    Hint. "Cara", A2:A6, 0.

  2. 2

    MATCH searches numbers just as well. Read =MATCH(36,B2:B6,0) and say what it returns.

    Hint. Fourth time down.

  3. 3

    The value can come from a cell. D2 holds a runner's name. In E4, find that runner's position in A2:A6, reading the name from the cell.

    Hint. D2, no quotation marks.

  4. 4

    The position is counted from the start of the range you give. Read =MATCH("Cara",A1:A6,0), this time with the heading included, and say what it returns.

    Hint. Start counting at the heading.

  5. 5

    In E6, write a formula with MATCH that gives the position of the slowest time, 45, in the times column.

    Hint. 45, B2:B6, 0.

  6. 6

    One thing about text. Read =MATCH("cara",A2:A6,0), the name typed in lower case, and say what it returns.

    Hint. Same name, small letter.