Excel: How to Use INDIRECT with INDEX MATCH

You can use the INDIRECT function with the INDEX and MATCH functions in Excel to look up specific values in rows and columns of one table and return the corresponding value from another table.

For example, you can use the following syntax to do so:


This particular formula looks up the value in the cell A11 in the column names of Table1 and the value in cell B10 in the “Team” column of Table1 and returns the value at the intersection of these values.

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

Example: How to Use INDIRECT with INDEX MATCH in Excel

Suppose we have a table in Excel that contains information about points scored by various basketball teams during three different games and another table in a different format where we would like to look up the points values for each team:

Suppose we would like to look up the game number and team name in the second table and return the appropriate points value from the first table.

We can type the following formula into cell B11 to do so:


The following screenshot shows how to use this formula in practice:

The formula returns 100, which is the value from the first table that corresponds to the Game 1 points value for the Mavs.

We can then click and drag this formula down to the remaining cells in column B, then across to each of the cells until we hit column G:

The formula correctly returns the points value for each combination of team name and game number.

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: Use INDEX and MATCH to Return Multiple Values Vertically

Leave a Reply

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