1. Core Concept: WHERE vs. WHAT
Unlike single functions that try to search and retrieve in one rigid step, INDEX + MATCH is a powerful two-function combination:
Searches for a value in a range and returns its relative position number (e.g. Row 3).
Takes a position number and extracts the value stored at that position.
2. 🔥 Live Interactive — Understand MATCH
Observe how MATCH returns only the relative row position (1, 2, 3...) of the item, not the item itself:
| Relative Index | Product ID (A) | MATCH Status |
|---|---|---|
| Index 1 | P101 | — |
| Index 2 | P102 | — |
| Index 3 | P103 | MATCH FOUND (Returns 3) |
| Index 4 | P104 | — |
| Index 5 | P105 | — |
3. 🔥 Live Interactive — Understand INDEX
Now observe how INDEX accepts a position number (e.g. 2) and retrieves the value stored at that coordinate:
| Position | Product (Col A) | Price ₹ (Col B) |
|---|---|---|
| Row 1 | Laptop Ultra | ₹55,000 |
| Row 2 | Mouse Pro | ₹1,500 |
| Row 3 | Monitor 4K | ₹18,000 |
| Row 4 | Keyboard Mech | ₹3,000 |
4. 🔥 Core Combined INDEX + MATCH Lookup
By placing MATCH inside INDEX, the position coordinate becomes dynamic:
➔ "Laptop"
➔ ₹55,000
| Product ID (lookup_range) | Product Name (return_range 1) | Category | Price ₹ (return_range 2) |
|---|---|---|---|
| P101 | Laptop | Electronics | ₹55,000 |
| P102 | Mouse | Electronics | ₹1,500 |
| P103 | Monitor | Electronics | ₹18,000 |
| P104 | Keyboard | Electronics | ₹3,000 |
| P105 | Chair | Furniture | ₹7,000 |
5. Visual Two-Stage Formula Breakdown
MATCH("P103", A2:A6, 0)
Returns Row 3
INDEX(C2:C6, 3)
Fetches 3rd item
"Monitor" / ₹18,000
6. 🔥 Left-Side Lookups (Overcoming VLOOKUP's Flaw)
Traditional VLOOKUP fails if the search column is to the right of the return column. INDEX + MATCH does not care about column order because the return array and search array are completely separate:
| Product Name (Col A - Return Left) | Product ID (Col B - Search Key) | Price ₹ (Col C) |
|---|---|---|
| Laptop Pro | P101 | ₹55,000 |
| Mouse Wireless | P102 | ₹1,500 |
| Monitor 27-inch | P103 | ₹18,000 |
7. F4 Absolute Range Reference Locking
When copying an INDEX + MATCH formula down multiple rows, use F4 to lock both the return range and lookup range with dollar signs:
8. 🔥 Live Multiple-Lookup Practice Lab
Observe how locking both ranges with F4 ensures consistent evaluation across multiple rows:
| Row | Target ID | Product [INDEX+MATCH] | Category [INDEX+MATCH] | Price ₹ [INDEX+MATCH] |
|---|---|---|---|---|
| 2 | P103 | Monitor | Electronics | ₹18,000 |
| 3 | P105 | Chair | Furniture | ₹7,000 |
| 4 | P101 | Laptop | Electronics | ₹55,000 |
9. Handling Missing Keys (#N/A) with IFERROR
When MATCH cannot locate the lookup key (e.g. P999), it throws #N/A. Wrap the formula with IFERROR to provide clean executive messages:
Output: #N/A
10. INDEX + MATCH Debugging Challenge
=INDEX(A2:A10, MATCH(E2, C2:C10, 0))
C2:C10) inside INDEX and ID (A2:A10) inside MATCH.=INDEX(C2:C10, MATCH(E2, A2:A10))
0.11. Exact Match (0) vs. Approximate Match (1, -1)
Always use 0 for standard business entity lookups. The other match types are used only for sorted ranges:
0: Exact Match on unsorted data.1: Approximate match (finds largest value ≤ lookup value, requires ascending sort).-1: Approximate match (finds smallest value ≥ lookup value, requires descending sort).
12. 🔥 Real Business Case (HR & Payroll Analysis)
Populate employee performance records from the HR master table using INDEX + MATCH:
| Emp ID | Employee Name [INDEX+MATCH] | Department [INDEX+MATCH] | Salary ₹ [INDEX+MATCH] |
|---|---|---|---|
| E101 | — | — | — |
| E102 | — | — | — |
| E103 | — | — | — |
| E104 | — | — | — |
13. INDEX + MATCH vs. VLOOKUP vs. XLOOKUP
| Capability | VLOOKUP | INDEX + MATCH | XLOOKUP |
|---|---|---|---|
| Lookup Left | ❌ No | ✅ Yes | ✅ Yes |
| Column Insertion Safe | ❌ No (breaks static index) | ✅ Yes (range adjusts) | ✅ Yes (range adjusts) |
| Legacy Excel Support | ✅ Excel 97+ | ✅ Excel 97+ (All Versions) | ❌ 365 / 2021 only |
| Exact Match Default | ❌ Must add FALSE | ❌ Must add 0 | ✅ Exact by default |
14. 🔥 Live Final Challenge (Inventory Valuation)
Join inventory SKU codes to product details and calculate Inventory Asset Value (Price × Stock):
| SKU Code | Product Name | Category | Stock Qty | Unit Price ₹ | Asset Valuation ₹ |
|---|---|---|---|---|---|
| SKU-001 | — | — | 12 | — | — |
| SKU-002 | — | — | 7 | — | — |
| SKU-003 | — | — | 35 | — | — |
| SKU-004 | — | — | 18 | — | — |
15. Quick Check & Knowledge Assessment
Knowledge Assessment: Excel INDEX + MATCH
Verify your understanding of coordinate-based lookups, left lookups, relative positions, and error trapping.
1. What does the MATCH function return in Microsoft Excel?
16. Accuracy & Official Specification
- INDEX:
=INDEX(array, row_num, [column_num]). Ifrow_numis 0, INDEX returns an array of the entire column. - MATCH:
=MATCH(lookup_value, lookup_array, [match_type]). Returns 1-based relative position withinlookup_array. - Exact Match:
match_type = 0ignores case and supports wildcard characters (?,*).