Excel Formula INDEX and Match Function

INDEX and MATCH functions are powerful tools in Excel used to look up values in a flexible way — often used as a better alternative to VLOOKUP.
Using INDEX and MATCH together in Excel is a powerful alternative to VLOOKUP, especially when you want more flexibility in your data lookup. Here's a detailed explanation:

---

🔹 Why use INDEX + MATCH instead of VLOOKUP?

- INDEX + MATCH can search left or right (VLOOKUP can only search right).
- It’s faster on large datasets.
- It allows more control over row and column positions.
Using INDEX and MATCH dynamically in Excel means you make the lookup flexible using cell references, dropdowns, or user input, instead of hardcoding values like names or numbers.

---

✅ Example: Dynamic Student Marks Lookup

| A (Name) | B (Maths) |
|----------|-----------|
| Ahmed | 80 |
| Ali | 90 |
| Bilal | 85 |
| Danish | 75 |

Suppose cell D1 is where you type a name (e.g. "Ali").

---
You can use cell references like:
=INDEX(B2:B5, MATCH(D1, A2:A5, 0))
Where D1 contains the name to search.
🔹 Formula (Dynamic Lookup):

=INDEX(B2:B5, MATCH(D1, A2:A5, 0))

How it works:

- D1 = "Ali" (user input)
- MATCH(D1, A2:A5, 0) → finds Ali's row = 2
- INDEX(B2:B5, 2) → returns 90

So, if you type any name in D1, the formula will automatically fetch that student's marks.

Видео Excel Formula INDEX and Match Function канала Naseer KhanKhel