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.
The following tutorials explain how to perform other common operations in Excel: