📊 Excel Basics #23 – MATCH() Function

The MATCH() function finds the position of a value within a range. It is especially powerful when combined with INDEX() to create flexible lookup formulas.

📌 What is the MATCH() Function?

MATCH() searches for a value and returns its relative position in a range.

Syntax:

=MATCH(lookup_value, lookup_array, [match_type])

The most commonly used option is:

0 → Exact match

📌 Example 1 – Find the Position

Consider:

A

Rahul

Priya

Amit

Neha

Formula:

=MATCH("Amit",A2:A5,0)

Result: 3

Why?

Within the range A2:A5:

1️⃣ Rahul

2️⃣ Priya

3️⃣ Amit

4️⃣ Neha

So Amit is in position 3.

📌 Example 2 – Using a Cell Reference

If cell E2 contains "Priya":

=MATCH(E2,A2:A5,0)

Result: 2

This makes the lookup dynamic because changing E2 changes the result.

📌 MATCH() Match Types

The third argument controls how Excel searches.

0 → Exact match

=MATCH(E2,A2:A10,0)

Use this for most business/data analysis scenarios.

1 → Approximate match, assuming the lookup array is sorted ascending.

-1 → Approximate match, assuming the lookup array is sorted descending.

⚠️ For beginners, use 0 unless you specifically need approximate matching.

📌 INDEX() + MATCH()

This is where MATCH() becomes extremely useful.

Example:

Employee | Department | Salary

Rahul | IT | 60000

Priya | HR | 55000

Amit | Finance | 70000

Neha | Marketing | 65000

To find Amit's salary:

=INDEX(C2:C5,MATCH("Amit",A2:A5,0))

How it works:

👉 MATCH() finds Amit's position → 3

👉 INDEX() returns the 3rd value from C2:C5 → 70000

Result: 70000

📌 MATCH() vs XLOOKUP()

MATCH():

• Returns the position.

• Very useful with INDEX().

• Useful when building dynamic formulas.

XLOOKUP():

• Directly returns the matching value.

• Easier for many modern lookup tasks.

• Available in newer Excel versions.

📌 Real-World Uses

• Find the position of an employee.

• Locate a product in a list.

• Find the position of a month or column.

• Build dynamic lookup formulas.

• Combine with INDEX() for advanced data analysis.

📌 Common Mistakes

❌ Forgetting the 0 for an exact match.

❌ Using approximate matching on unsorted data.

❌ Searching in the wrong range.

✅ Best Practices

• Use 0 for exact matching in most cases.

• Combine MATCH() with INDEX() for flexible lookups.

• Keep the lookup range consistent with the data you're searching.

• Use XLOOKUP() when you simply need to return a matching value.

💡 Remember:

MATCH() answers:

👉 "Where is this value?"

INDEX() answers:

👉 "What value is at this position?"

Together, they form one of Excel's most powerful lookup combinations.

Double Tap ❤️ For More