Lookup
MATCH
Definition
MATCH returns the relative position of a value within a single row or column of cells.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])
Arguments
| Argument | Meaning |
|---|---|
lookup_value |
The value to find, such as a word in quotes or a cell reference. |
lookup_array |
A single row or column to search, such as A2:A5. |
match_type |
Use 0 for an exact match. The default of 1 expects ascending order and finds the largest value not above the key. |
Worked example
A grocery list is stored in A2:A5, and you want to know where Pear sits in it.
| A: Fruit |
|---|
| Fig |
| Plum |
| Pear |
| Lime |
Formula: =MATCH("Pear",A2:A5,0)
Result: 3. Pear is the third entry inside the range A2:A5.
Practice
Range B2:B6 holds these cities in order: Oslo, Rome, Lima, Cairo, and Seoul. Write a formula that returns the position of Cairo.
- In C2, write a MATCH formula that returns the position of Cairo in B2:B6.
- C2 should show the position of Cairo in B2:B6.
Step 1 of 2
Enter =MATCH("Cairo",B2:B6,0) in C2.
Cairo is the fourth item, so the position is 4.
Formulas here use English function names, such as SUM, and a period as the decimal separator, in every language.
Loading the sheet…
C2 uses MATCH.
C2 now shows 4.
Related functions
MATCH reports a position that INDEX can use to fetch a value, which is the same job VLOOKUP does in one step.
Common mistake
Leaving out the 0 on an unsorted text list; the default match type assumes ascending order and can return a wrong position or #N/A.