Excel INDEX function

How to use the Excel INDEX function

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:

= INDEX(lookup_array,MATCH(lookup_value,lookup_column, 0), col_ind))

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

How to use the Excel VLOOKUP function

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.

How to use the Excel MATCH function

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.

How to use the Excel XMATCH function

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.

How to use the Excel XLOOKUP function

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.

Facebook
Twitter
LinkedIn
Reddit
WhatsApp
Email