How to use MATCH in Excel
MATCH tells you where an item appears in a list. It returns a position number, not the item itself. You can combine it with INDEX to return a value from another column.
Syntax
=MATCH(lookup_value, lookup_array, [match_type])- lookup_value: the item you want Excel to find.
- lookup_array: the row or column where Excel should look.
- match_type: use 0 to find an exact match. The other options are for lists sorted in a particular order. If you are unsure, use 0.
Exact-match example
Enter these products in cells A1:A4. The cell references appear above the table, and the product names go in column A.
| Cell | A |
|---|---|
| 1 | Product |
| 2 | Keyboard |
| 3 | Mouse |
| 4 | Monitor |
To find the position of Monitor in the product list, enter:
=MATCH("Monitor", A2:A4, 0)The answer is 3. Monitor is the third item in the selected range A2:A4.
Use MATCH with INDEX
Add prices in column B and type Monitor in cell D2. This formula finds Monitor in column A and returns the price from the same row in column B:
=INDEX(B2:B4, MATCH(D2, A2:A4, 0))MATCH finds the item's position. INDEX returns the value at that position in the price list. See the INDEX tutorial for more examples.
Common mistakes
- Include 0 as the last argument when you want an exact match. If you leave it out, Excel uses approximate matching.
- The lookup range must be a single row or a single column.
- Approximate matching needs the list sorted in the correct order. If you are learning MATCH, use 0 for an exact match.
- MATCH does not distinguish uppercase and lowercase letters. With 0, the characters
*and?act as wildcards. Put~before either character to search for it literally.
Related guides: LOOKUP in Excel · INDEX in Excel · All tutorials.