📊 Excel Basics #24 – INDEX() + MATCH() – Powerful Dynamic Lookup

"INDEX()" and "MATCH()" are often used together to create flexible lookup formulas. Before "XLOOKUP()", this combination was one of the most popular alternatives to "VLOOKUP()".

📌 How Does It Work?

Think of it this way:

👉 "MATCH()" → Finds where the value is.

👉 "INDEX()" → Returns what is at that position.

Together:

=INDEX(return_range,MATCH(lookup_value,lookup_range,0))

📌 Example – Find an Employee's Salary

Employee Department Salary

Rahul IT 60000

Priya HR 55000

Amit Finance 70000

Neha Marketing 65000

Suppose cell E2 contains: "Amit"

Formula:

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

Result: 70000

📌 Step-by-Step

First, "MATCH()" searches for Amit:

=MATCH(E2,A2:A5,0)

Result: 3

Amit is the 3rd employee in the range.

Then "INDEX()" uses that position:

=INDEX(C2:C5,3)

Result: 70000

The combined formula performs both steps automatically.

📌 Why Use INDEX() + MATCH()?

Compared with traditional "VLOOKUP()":

✅ Can look left or right.

✅ Doesn't require a column index number.

✅ More flexible when columns are inserted or rearranged.

✅ Works well for dynamic lookup scenarios.

📌 Two-Way Lookup

"INDEX()" + "MATCH()" can also find a value based on both a row and a column.

Example:

Employee Jan Feb Mar

Rahul 50000 55000 60000

Priya 45000 50000 52000

Amit 60000 65000 70000

Suppose: E2 = Amit, F2 = Feb

Formula:

=INDEX(B2:D4,MATCH(E2,A2:A4,0),MATCH(F2,B1:D1,0))

Result: 65000

Here:

👉 First MATCH() finds the employee row.

👉 Second MATCH() finds the month column.

👉 INDEX() returns the value at their intersection.

📌 Real-World Uses

• Employee salary lookup.

• Product price lookup.

• Customer information retrieval.

• Monthly sales analysis.

• Two-dimensional reporting.

• Dynamic dashboards.

📌 INDEX + MATCH vs XLOOKUP

"INDEX() + MATCH()":

• Very flexible.

• Works in older Excel versions.

• Excellent for advanced lookup logic.

"XLOOKUP()":

• Easier to write.

• Supports built-in "not found" handling.

• Can perform both vertical and horizontal lookups.

• Preferred in newer Excel versions.

📌 Common Mistakes

❌ Forgetting the "0" in "MATCH()" for exact matching.

❌ Using ranges with different sizes.

❌ Referencing the wrong row or column range.

✅ Best Practices

• Use exact matching ("0") for most business lookups.

• Keep lookup ranges consistent.

• Use absolute references when copying formulas.

• Use "XLOOKUP()" when it provides a simpler solution.

💡 Remember:

MATCH() → Find the position

INDEX() → Return the value

INDEX + MATCH → Find the right value dynamically

Mastering this combination is an important Excel skill for data analysts and interview preparation.

Double Tap ❤️ For More