Description
The Excel INDEX function returns the value of an element in a range or array at a given position, specified by the row and column number indexes. INDEX is commonly used in combination with the MATCH function for powerful and version-independent lookup operations.
Syntax
INDEX( array, row_num, [col_num] )
Arguments
array – A range of cells or an array
row_num – An integer value representing the row number index
col_num – [Optional] An integer value representing the column number index
Return value
A value found at the given position
How to use the INDEX function in Excel
Use the Excel INDEX function to return a value at a given position in a range or array. For the proper use of this function, you have to supply a range of cells for the array argument, and at least one value for either the row_num or the column_num argument.
In the above example, we get the value at row number 6 and column number 3 using the following formula:
= INDEX(B7:E14, 6, 3) // returns element at relative position (6,3)
If you use the INDEX statement as the result in a cell, you will see the respective value as output. It is important to mention that INDEX returns a reference to the respective cell, so there are much more applications for this function than simply extracting a value.
INDEX and MATCH
The Excel INDEX function is commonly used together with the MATCH function. In this combination, MATCH covers the task of locating and feeding a position to the INDEX function, which then returns the value at that given position. The basic syntax of INDEX MATCH looks like this:
In the above statement, MATCH locates the position of a lookup_value in the lookup_column, which is then used as the row_num index in the INDEX function. With a given lookup_array and col_ind for the result column, INDEX is then able to perform a flexible lookup that is even superior to the VLOOKUP function.
One major reason why INDEX MATCH is often preferred over VLOOKUP function is the fact that you can perform lookup to the left and right, while VLOOKUP is limited to only look up to the right.
Additional Notes
In general, the row_num and the column_num arguments must point to a cell within the given array. Otherwise, INDEX returns a #REF! error.
Formula examples
Related Tutorials
Related Functions
Excel VLOOKUP function
The Excel VLOOKUP function searches for a value by matching on the first table column and returns the corresponding value from a column in the same row.
Excel MATCH function
The Excel MATCH function searches for a specific item in a given range and returns the position as index number. Use MATCH and INDEX for powerful lookups.
Excel XMATCH function
The Excel XMATCH function searches for a specific item in a given range and returns the position. XMATCH is a more flexible and robust version of MATCH.
Excel XLOOKUP function
The Excel XLOOKUP function searches an array, and returns an item corresponding to the first match it finds. It replaces many older lookup functions.
