Excel + Google Sheets · MATCH
Find the position of any value
MATCH tells you which position a value occupies in a list. 0 means exact match.
MATCH tells you where something is. You give it a value and a list, and it answers with the position of that value in the list: first, second, third. On its own it returns just a number, but that number is exactly what other functions such as INDEX need to fetch the value you want.
MATCH works in Excel 2010 or later, in Excel 365 and in Google Sheets. It is the second half of the classic INDEX plus MATCH lookup that works in every version of Excel. Read the INDEX guide to see the first half, or the INDEX and MATCH guide for the full combination.
How it works MATCH
MATCH takes three pieces of information: what you look for, the range where you look, and the match type. Write the formula like this:
=MATCH(C2,A2:A5,0)Here Excel looks for the value in C2 inside A2:A5 and returns its position. The final 0 asks for an exact match, which is what you want almost every time. Without it, Excel assumes the list is sorted and can return a wrong position.
Example step by step
- Type four client names in A2:A5.
- Type the name you are looking for in C2, for example Priya.
- Click D2, type
=MATCH(C2,A2:A5,0)and press Enter. - Change the name in C2 and watch the position change.
| Client | Find | Position |
|---|---|---|
| Laura | Priya | 3 |
| João | ||
| Priya | ||
| Tom |
The result is 3, because Priya is the third name in the list. If the name appears twice, MATCH returns the first position. If it does not exist, Excel shows #N/A, which you can replace with IFERROR.
Common errors
- The last argument is missing and the position is wrong. Always write 0 when you need an exact match.
- The value has an extra space and MATCH shows #N/A. Clean it with TRIM first.
- The name appears twice and you expect the second one. MATCH always returns the first position.
- The range includes the header row and every position is off by one. Start the range at the first data row.
Wrap the formula in IFERROR to show a friendly message when the value does not exist. Together with INDEX it becomes a lookup that never breaks when you add or move columns. MATCH works in Excel 2010 or later, Excel 365 and Google Sheets.