Table of Contents
INDEX MATCH is a powerful and flexible formula in Excel that allows users to retrieve data from a specific cell in a table based on specific criteria. This formula can also be used to retrieve data from another sheet in the same workbook by referencing the sheet name in the formula. By using the sheet name in the INDEX and MATCH functions, users can easily retrieve data from other sheets without having to manually switch between sheets. This feature makes INDEX MATCH a convenient and efficient tool for organizing and analyzing data in Excel.
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:
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: