MATCH

What it does

MATCH looks for a value in one row or column and tells you its position: 1 for the first item, 2 for the second, and so on. It doesn't return the value itself, which is why it is usually wrapped in INDEX.

Syntax

=MATCH(lookup_value, lookup_array, [match_type])

Arguments in square brackets are optional, and … means you can add more of the same. In this Excel language, arguments are separated by ,

Arguments

lookup_value

What to look for, usually a cell.

lookup_array

The single row or column to search.

match_type

0 for an exact match. 1, or leaving it out, finds the largest value that is less than or equal in a list sorted smallest first; -1 does the opposite for a list sorted largest first.

Example

Kai is on the guest list. At what position?

The formula in D2:

=MATCH(C2,A2:A5,0)

Result: 3

Kai is the third name in A2:A5, so the result is 3. The position counts from the start of the range, not from row 1 of the sheet.

Common mistakes

• Leaving out the 0. The default is an approximate match on a sorted list, so on an unsorted list it can return a wrong position without any error.
• Searching a range with several columns. MATCH needs one row or one column.
• Expecting the value back. MATCH returns a position; use INDEX to turn it into a value.

In other Excel languages

Excel translates function names, and in some languages the argument separator too. Type the name your Excel shows.

Practise it

• MATCH: find the positionModule 6 · INDEX and MATCH
• INDEX and MATCH togetherModule 6 · INDEX and MATCH
• A sales reportModule 14 · Combined skills

More from this module

• INDEX
• XMATCH

All functions