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

By Ali, Founder, Excel Lessons

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.

Read more about this

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 the runners, 0 an exact match.

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 the runners, 0 an exact match.

    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 D2's runner in A2:A6.

    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) and say what it returns. This time the heading is included.

    Hint. Start counting at the heading.

  5. 5

    In E6, find the position of the slowest time, 45, in B2:B6.

    Hint. 45, B2:B6, 0.

  6. 6

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

    Hint. Same name, small letter.