How to Use INDEX in Excel: Syntax, Examples, and Tips

The INDEX function in Excel returns the value of a cell at the intersection of a specified row and column within a range or array. You use it by providing the range, the row number, and optionally the column number to get the exact value you want.

Syntax

The INDEX function has two main forms: array form and reference form. The most common is the array form:

• INDEX(array, row_num, [column_num])

Where:

• array: The range or array to look in.
• row_num: The row number in the array to return a value from.
• column_num (optional): The column number in the array to return a value from. If omitted, it defaults to the first column.

excel

INDEX(array, row_num, [column_num])

Example

This example shows how to get the value from the 2nd row and 3rd column of a table.

excel

A1:C3
1	2	3
4	5	6
7	8	9

Formula in cell E1:
=INDEX(A1:C3, 2, 3)

Output

6

Common Pitfalls

Common mistakes when using INDEX include:

• Using a row_num or column_num that is zero or greater than the size of the array, which causes an error.
• Omitting the column_num when the array has multiple columns and expecting a value from a different column than the first.
• Confusing the INDEX function with VLOOKUP or HLOOKUP, which search for values rather than returning by position.

Example of wrong and right usage:

excel

Wrong: =INDEX(A1:C3, 4, 1)  // Row 4 does not exist, returns #REF!
Right: =INDEX(A1:C3, 3, 1)  // Returns 7

Quick Reference

Parameter	Description
array	The range or array to get data from
row_num	The row number in the array to return
column_num	Optional. The column number in the array to return (defaults to 1)

Key Takeaways

Use INDEX to get a value by specifying row and column numbers in a range.

Always ensure row and column numbers are within the range size to avoid errors.

If column_num is omitted, INDEX returns the value from the first column.

INDEX returns a value by position, not by matching content like lookup functions.

Combine INDEX with other functions for powerful data retrieval.