INDEX
Returns a value from a table based on row and column numbers
=INDEX(array, row_num, [column_num])This static page keeps the function indexed for search, while the spreadsheet app handles interactive exploration and saved formulas.
What the function does
Overview
Returns a value from a table based on row and column numbers
The INDEX function returns a value or the reference to a value from within a table or range. It is most powerful when used in its 'array' form to retrieve data based on specific coordinates. Unlike VLOOKUP, INDEX allows for 'left' lookups and is computationally more efficient because it doesn't require the entire table array to be recalculated if only one coordinate changes. A key technical feature is that if um or um is set to 0, INDEX returns an array of values for the entire column or row respectively. This behavior is essential for creating dynamic ranges or supplying entire vectors to other functions like SUM or AVERAGE without using volatile functions like OFFSET. In modern Excel, INDEX is also fully compatible with dynamic arrays, meaning it can return multiple values if the input array is multi-dimensional.
Quick reference
Syntax
=INDEX(array, row_num, [column_num])Inputs
Arguments
Example: A1:D10
Example: 3
Example: 2
Formula patterns
Examples
Basic coordinate lookup
=INDEX(A2:C10, 3, 2)Two-way lookup with MATCH
=INDEX(A2:E10, MATCH(G2, A2:A10, 0), MATCH(H2, A2:E2, 0))Return entire row for calculation
=SUM(INDEX(A2:D10, 5, 0))Avoid these issues
Common Errors
#REF!
Cause: The row_num or column_num arguments refer to a cell outside the bounds of the specified range.
Fix: Verify the dimensions of the array and ensure your index numbers do not exceed the total count of rows or columns in that range.
#VALUE!
Cause: One or more of the numeric arguments (row_num or column_num) are non-numeric or cannot be interpreted as a number.
Fix: Ensure that the cells or formulas providing the row and column indices return valid integers rather than text or error values.
Platform support
Compatibility
Source: Microsoft Support
Common questions
Frequently Asked Questions
Returns a value from a table based on row and column numbers
array: The range to search in um: Row number within the array um: Column number within the array
#REF!: Verify the dimensions of the array and ensure your index numbers do not exceed the total count of rows or columns in that range. #VALUE!: Ensure that the cells or formulas providing the row and column indices return valid integers rather than text or error values.