The INDEX function returns a value from a specific position in a table or range based on row and column numbers.
It is commonly used for:
- Advanced lookups
- Dynamic reports
- Data extraction
- Combining with MATCH function
Structure of INDEX
There are two main forms:
Array Form (Most Common)
=INDEX(array, row_num, [column_num])
Arguments:
- array → The data range
- row_num → Row number in the range
- column_num → Column number in the range (optional if single column)
Example 1: Return Value from Table
If A1:C5 contains data:
=INDEX(A1:C5, 2, 3)
This returns the value from:
- Row 2
- Column 3
Example 2: Single Column Range
If A1:A5 contains data:
=INDEX(A1:A5, 4)
Returns the value from row 4.
Using INDEX with MATCH (Powerful Combination)
INDEX is often combined with MATCH for dynamic lookups.
Example:
=INDEX(B5:B10, MATCH(A2, A5:A10, 0))
How it works:
- MATCH finds the position of A2 in A5:A10
- INDEX returns the corresponding value from B5:B10
This works like VLOOKUP but is more flexible.
Why INDEX is Better Than VLOOKUP
- Can look left or right
- No column index counting problem
- More efficient for large datasets
- Works well with MATCH
Common Errors
- #REF! → Row or column number is outside range
- #VALUE! → Wrong arguments
- #N/A → MATCH cannot find value
Practical Uses
- Dynamic dashboards
- Two-way lookups
- Extracting specific records
- Building advanced reports
Important Tip
When using INDEX with MATCH, always use:
MATCH(value, range, 0)
0 ensures exact match.
Conclusion
The INDEX function is one of the most powerful lookup tools in Excel, especially when combined with MATCH.
It provides flexibility, accuracy, and better performance for professional data analysis tasks.