When working with lists or tables in Excel, you often need to find the position of a value. For example, which row is “Banana” in? Or where does the number 50 appear in a list?
The MATCH function makes this easy by returning the position of a value in a range. It doesn’t return the actual value, but the position number—which becomes very powerful when combined with the INDEX function.
🔍 What is the MATCH Function?
The MATCH function returns the relative position of a value in a range.
Syntax:
-
lookup_value → The value you want to find.
-
lookup_array → The range of cells to search in.
-
match_type → (optional)
-
0 = Exact match (most common).
-
1 = Less than (values must be sorted ascending).
-
-1 = Greater than (values must be sorted descending).
-
💡 If omitted, match_type defaults to 1 (approximate match).
✅ Example 1: Exact Match
| A1 |
|---|
| Apple |
| Banana |
| Cherry |
Formula:
Result: 2
✔️ Explanation: Banana is in the second row of the range, so MATCH returns 2.
✅ Example 2: Approximate Match (Sorted List)
| A1 |
|---|
| 10 |
| 20 |
| 30 |
Formula:
Result: 2
✔️ Explanation: MATCH looks for 25 in the list. Since it’s not found, it returns the position of the largest value less than 25 → 20 (row 2).
✅ Example 3: Greater Than Match (Descending Order)
| A1 |
|---|
| 100 |
| 90 |
| 80 |
Formula:
Result: 2
✔️ Explanation: With -1, MATCH finds the smallest value greater than 85 → 90 (row 2).
✅ Example 4: MATCH with INDEX (Powerful Lookup)
MATCH really shines when used with INDEX.
| A1 | B1 |
|---|---|
| Apple | 10 |
| Banana | 20 |
| Cherry | 30 |
Formula:
Result: 30
✔️ Explanation:
-
MATCH(“Cherry”, A1:A3, 0) → 3
-
INDEX(B1:B3, 3) → 30
This creates a flexible lookup formula similar to VLOOKUP, but more powerful.
🎯 Practical Uses of MATCH
-
Find item positions in lists (e.g., product names, IDs).
-
Dynamic lookups when combined with INDEX.
-
Flexible alternative to VLOOKUP (works both left-to-right and right-to-left).
-
Search within sorted data using approximate match.
📝 Conclusion
The MATCH function is a simple yet powerful way to locate positions in Excel. On its own, it helps you find the row or column number of a value. When paired with INDEX, it becomes one of the most useful lookup tools—often better than VLOOKUP for complex data.
👉 Master MATCH, and you’ll unlock a new level of flexibility in your spreadsheets.
