Excel functions / Lookup & reference
MATCH
Returns the position of a value in a list.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])Arguments
lookup_value | What you are searching for. |
|---|---|
lookup_array | The single column or row to search. |
match_type | 0 for an exact match. Always use 0 unless the list is sorted and you want the nearest value. |
How it works
MATCH does not return the value itself. It tells you where it is: 1 for the first item, 2 for the second, and so on.
That position is exactly what INDEX needs, which is why the two are so often used together.
Example
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Code | Product | Price | Stock | ||
| 2 | A10 | Lamp | $25.00 | 12 | 3 | |
| 3 | B20 | Desk | $140.00 | 3 | ||
| 4 | C30 | Chair | $85.00 | 7 | ||
| 5 | D40 | Shelf | $60.00 | 0 | ||
| 6 |
Result: 3. C30 is the 3rd code in the list.
Tips
- Forgetting the final 0 is the most common MATCH mistake.
- MATCH is not case-sensitive: "desk" finds Desk.
Practise MATCH
Try it yourself in a real spreadsheet, with instant feedback on every answer.