The MATCH function searches for a specified value in a range and returns the position number of that value.
It does not return the actual value — it returns the position.
MATCH is commonly used with the INDEX function for advanced lookups.
Structure of MATCH
=MATCH(lookup_value, lookup_array, [match_type])
Arguments:
- lookup_value → Value you want to find
- lookup_array → Range where Excel searches
- match_type → Type of match
Match Types
| Match Type | Meaning |
|---|---|
| 0 | Exact match (most commonly used) |
| 1 | Exact or next smallest (ascending order required) |
| -1 | Exact or next largest (descending order required) |
Example 1: Exact Match
If A1:A5 contains:
Apple
Banana
Mango
Orange
Grapes
Formula:
=MATCH("Mango", A1:A5, 0)
Result: 3
Because Mango is in the 3rd position.
Example 2: Using Cell Reference
If B1 contains “Orange”:
=MATCH(B1, A1:A5, 0)
Returns the position of Orange.
Example 3: Approximate Match
If A1:A5 contains numbers sorted ascending:
10
20
30
40
50
=MATCH(25, A1:A5, 1)
Result: 2
Because 20 is the closest smaller value.
MATCH with INDEX (Powerful Combination)
=INDEX(B1:B5, MATCH("Mango", A1:A5, 0))
How it works:
- MATCH finds position of Mango
- INDEX returns value from B1:B5 at that position
This is more flexible than VLOOKUP.
Common Errors
- #N/A → Value not found
- #VALUE! → Incorrect arguments
Why MATCH is Important
- Finds position dynamically
- Works vertically or horizontally
- Makes INDEX dynamic
- More flexible than VLOOKUP
Conclusion
The MATCH function is essential for advanced lookups and dynamic reports. When combined with INDEX, it becomes one of the most powerful data retrieval methods in Excel.