Back to All Formulas
Lookup & ReferenceLevel 4: Green (Lookups & Data Models)Difficulty: Intermediate
How to Use INDEX & MATCH in Excel
The robust two-part lookup formula that looks left, looks up, and never breaks when new columns are inserted.
Formula Syntax
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Arguments Breakdown
| Argument | Requirement | Purpose & Details |
|---|---|---|
| return_range | Required | The column or row holding the value you want to get. |
| lookup_value | Required | The key value you want to locate. |
| lookup_range | Required | The single row or column where the key is located. |
| match_type | Optional | 0 for exact match. |
Step-by-Step Practical Business Example
Suppose you have the following dataset in Excel cells A1:D4:
| Department | Manager | Branch Code |
|---|---|---|
| Logistics | Karan Johar | BR-09 |
| Finance | Meera Sen | BR-14 |
| Sales | Sunil Yadav | BR-22 |
Applied Formula:Result: Meera Sen
=INDEX(B2:B4, MATCH("BR-14", C2:C4, 0))
How it works: Matches 'BR-14' in Column C to row 2, and INDEX fetches the manager from Column B (looking left).
Common Errors & How to Fix Them
#N/A
Why it happens: MATCH could not locate the lookup value in the lookup range.
Fix: Check for hidden leading or trailing spaces using TRIM().
Ready to Master Excel?
Explore 7 LevelsMaster INDEX & MATCH in Level 4
Learn INDEX & MATCH alongside 500+ formulas, hands-on practice workbooks, and verifiable certificates.