The combination of INDEX + MATCH is one of the most powerful lookup techniques in Excel. It is more flexible and efficient than VLOOKUP.
This method allows you to:
- Lookup left or right
- Perform dynamic lookups
- Handle large datasets efficiently
- Avoid column number counting
Basic Structure
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
How It Works:
- MATCH finds the position of the lookup value.
- INDEX returns the value from that position.
Example 1: Basic Lookup
If:
A5:A10 → Employee IDs
B5:B10 → Employee Names
Formula:
=INDEX(B5:B10, MATCH(A2, A5:A10, 0))
MATCH finds position of A2 in A5:A10
INDEX returns corresponding name from B5:B10
Why Use INDEX + MATCH Instead of VLOOKUP?
| VLOOKUP | INDEX + MATCH |
|---|---|
| Cannot lookup left | Can lookup left or right |
| Needs column number | No column number required |
| Slower with large data | More efficient |
| Breaks if columns inserted | More stable |
Example 2: Lookup to the Left
If:
A5:A10 → Names
B5:B10 → IDs
To find ID based on Name:
=INDEX(B5:B10, MATCH(A2, A5:A10, 0))
This works even if return column is on the left.
Two-Way Lookup (Row and Column Match)
If you have a table:
| Jan | Feb | Mar | |
|---|---|---|---|
| Ali | 100 | 120 | 130 |
| Sara | 150 | 160 | 170 |
Formula:
=INDEX(B2:D3, MATCH(A5, A2:A3, 0), MATCH(B5, B1:D1, 0))
First MATCH finds row
Second MATCH finds column
INDEX returns exact value
Important Tips
- Always use 0 in MATCH for exact match
- Ensure ranges are same size
- Use IFERROR to handle errors
Example:
=IFERROR(INDEX(B5:B10, MATCH(A2, A5:A10, 0)), "Not Found")
Common Errors
- #N/A → Value not found
- #REF! → Range mismatch
- #VALUE! → Wrong formula format
Conclusion
INDEX + MATCH is a professional-level lookup solution in Excel.
It is flexible, powerful, and recommended for advanced data analysis and reporting tasks.