Excel: How to Use INDEX MATCH from Another Sheet


You can use the following syntax in Excel to use INDEX MATCH from another sheet:

=INDEX(Sheet2!$B$2:$C$11,MATCH(A2,Sheet2!$A$2:$A$11,0),2)

This particular formula looks up the value in cell A2 of the current sheet within the range A2:A11 of Sheet2 and then returns the value from column 2 in the range B2:C11 of Sheet2.

The following example shows how to use this syntax in practice.

Example: How to Use INDEX MATCH from Another Sheet in Excel

Suppose we have the following sheet named Sheet1 that contains the names of various basketball teams:

And suppose we have another sheet named Sheet2 that contains information about the points and assists for various basketball teams:

Suppose we would like to look up each team name from Sheet1 within Sheet2 and return the value from the Assists column in Sheet2.

To do so, we can type the following formula into cell B2 of Sheet1:

=INDEX(Sheet2!$B$2:$C$11,MATCH(A2,Sheet2!$A$2:$A$11,0),2)

We can then click and drag this formula down to each remaining cell in column B:

Excel INDEX MATCH from another sheet

Column B in Sheet1 now contains the value from the Assists column in Sheet2 that corresponds to each team name in column A.

Note that the last argument in the formula of 2 specifies that we would like to return the value from column 2 in the range B2:C11 on Sheet2, which is the Assists column.

If we would instead like to return the value from the Points column, we could change the last value in the formula to 1:

=INDEX(Sheet2!$B$2:$C$11,MATCH(A2,Sheet2!$A$2:$A$11,0),1)

This would return the value from the Points column in Sheet2 that corresponds to each team name:

Additional Resources

The following tutorials explain how to perform other common operations in Excel:

Excel: How to Use IF Function with INDEX MATCH
Excel: How to Use SUM with INDEX MATCH
Excel: How to Use INDEX MATCH to Return Multiple Values

Leave a Reply

Your email address will not be published. Required fields are marked *