MS Excel: INDEX function, from Basic to Advanced

The INDEX function is one of Excel’s most powerful and versatile tools, especially when working with structured data. Whether you're just starting or looking to level up your spreadsheet game, mastering INDEX can significantly improve your data analysis skills.

The INDEX function returns the value of a cell at the intersection of a given row and column in a specified range.

Syntax:

INDEX(array, row_num, [column_num])

• array: The range of cells or an array constant.

• row_num: The row in the array from which to return a value.

• [column_num] (optional): The column in the array from which to return a value.

Basic Examples

1. Return a Single Cell Value

Suppose you have this table:

A	B	C
Apple	10	Red
Banana	15	Yellow
Grape	12	Purple

=INDEX(A1:C3, 2, 1)

Returns: Banana(2nd row, 1st column)

=INDEX(A1:C3, 3, 3)

Returns: Purple

Intermediate Usage

2. Dynamic Lookup with MATCH

Combine INDEX with MATCH for dynamic lookups. This is a great alternative to VLOOKUP.

=INDEX(B2:B4, MATCH("Banana", A2:A4, 0))

Returns: 15

Explanation:

• MATCH("Banana", A2:A4, 0) returns 2 (position of "Banana").

• INDEX(B2:B4, 2) returns the 2nd value in B2:B4.

3. INDEX for 2D Ranges

=INDEX(A2:C4, MATCH("Banana", A2:A4, 0), MATCH("Color", A1:C1, 0))

If headers are:

• A1: Fruit

• B1: Quantity

• C1: Color

Returns: Yellow

This makes INDEX extremely flexible for matrix-style lookups.

Advanced Usage

4. INDEX with Multiple Criteria

When working with multiple criteria, you can use INDEX in an array formula:

=INDEX(C2:C5, MATCH(1, (A2:A5="Apple")*(B2:B5=10), 0))

Press Ctrl+Shift+Enter in legacy Excel versions (not needed in Excel 365/2021).

Returns: Value from C2:C5 where A = "Apple" and B = 10.

5. INDEX as a Range Reference

You can use INDEX to return a cell reference rather than a value—useful in dynamic range definitions.

=SUM(A1:INDEX(A1:A10, 5))

Returns the sum from A1 to A5.

Common Errors and Troubleshooting

Error	Cause	Solution
#REF!	Row or column number is out of range	Make sure row_num and column_num do not exceed the size of the array
#VALUE!	Incorrect argument type	Check for non-numeric inputs where numbers are required
#NAME?	Typo in function name	Double-check spelling
Wrong output	Misaligned range in MATCH	Ensure the array in INDEX matches the output of MATCH

Tips and Best Practices

• Use INDEX with MATCH instead of VLOOKUP for better performance and flexibility.

• Pair with MATCH and IF/FILTER for complex logic.

• Use INDEX inside INDIRECT or OFFSET for advanced referencing.

• Remember that INDEX is non-volatile (unlike OFFSET), so it's better for large spreadsheets.

Conclusion

The INDEX function is more than just a basic lookup tool—it's a gateway to powerful, flexible Excel solutions. When combined with functions like MATCH, IF, and SUM, you can create dynamic reports, dashboards, and analysis tools with ease.