How can I use INDEX MATCH from another sheet in Excel?

By /

INDEX MATCH is a powerful function in Excel that allows users to retrieve data from a specific cell in a table based on a matching value in another cell. This function can be used across multiple sheets in Excel, making it a valuable tool for organizing and analyzing data. By using INDEX MATCH from another sheet, users can easily access data from a different sheet in their workbook without having to manually navigate between sheets. This feature can save time and increase efficiency in data manipulation and analysis.

Excel: 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:

x

Related terms:

  1. How can INDEX MATCH be used from another sheet in Excel?
  2. How can I use INDEX MATCH to return the maximum value in Excel?
  3. How to Use SUM with INDEX MATCH in Excel?
  4. How to use INDEX MATCH Across Multiple Columns in Excel?
  5. How Can INDEX MATCH Be Used to Return the Maximum Value in Excel?
  6. How can I use INDEX MATCH in Google Sheets to retrieve multiple results?
  7. How do I use INDEX MATCH with Multiple Criteria in VBA?
  8. How can I use INDEX MATCH in VBA?
  9. How to use Google Sheets: INDEX MATCH with Multiple Criteria
  10. How can I use SUM with INDEX MATCH in Google Sheets?