Quick Navigation

INDEX Function

Excel 2007+

Summary

The INDEX function retrieves a value or reference from a specified position within a range or array. It's a cornerstone of Excel lookup operations, enabling precise data extraction by row and column coordinates.

Syntax

INDEX(array, row_num, [column_num]) or INDEX(reference, row_num, [column_num], [area_num])

Parameters

Parameter	Type	Required	Description
array/reference	`Range/Array`	Yes	The data range or array to search within
row_num	`Number`	Yes	Row index to retrieve (1-based)
column_num	`Number`	No	Optional column index to retrieve (1-based)
area_num	`Number`	No	Optional area selector for multiple ranges

Using the INDEX Function

INDEX excels at positional data retrieval, making it perfect for dynamic lookups when combined with MATCH. Use Array form for single ranges and Reference form for multiple non-contiguous ranges.

Common INDEX Examples

Basic Table Lookup

=INDEX(A2:B3,2,2)

Returns 'Pears' from row 2, column 2 of range A2:B3

Dynamic Column Retrieval

=INDEX(A1:C10,0,3)

Returns entire 3rd column as array (use Ctrl+Shift+Enter in older Excel)

Two-Way Lookup

=INDEX(B2:B10,MATCH("Bananas",A2:A10,0))

Finds price of Bananas using INDEX+MATCH combination

Multiple Area Reference

=INDEX((A1:C6,A8:C11),2,2,2)

Returns value from 2nd row, 2nd column of 2nd area (A8:C11)

Dynamic Range End

=SUM(B2:INDEX(B2:B10,COUNT(A2:A10),1))

Sums entire column B up to last data row

Frequently Asked Questions

Array form returns values; Reference form returns cell references usable in other formulas.

Zero returns the entire row (0 row_num) or column (0 column_num) as an array.

Yes, INDEX+MATCH is more flexible and faster, especially for left-to-right lookups.

Common Errors and Solutions

#REF!

Cause: row_num or column_num exceeds array dimensions

Solution: Verify indices are within range boundaries

#VALUE!

Cause: Areas in reference form span different sheets

Solution: Keep all reference areas on same worksheet

Single value from array formula

Cause: Not entered as array formula when returning row/column

Solution: Use Ctrl+Shift+Enter or dynamic arrays in Microsoft 365

Notes

Row and column numbering starts at 1
Use 0 to return entire row/column
Reference form enables dynamic range construction
Array form works with constants like {1,2;3,4}
Microsoft 365 supports dynamic array output natively

Compatibility

Available in: Excel 2007, Excel 2010, Excel 2013, Excel 2016, Excel 2019, Excel 2021, Microsoft 365

Not available in:

Content last reviewed: December 9, 2025
Update frequency: As needed
Excel versions tested: Excel 2007+