How can I use the INDIRECT function in combination with INDEX MATCH in Excel?

By /

The INDIRECT function in combination with INDEX MATCH in Excel allows users to retrieve data from a specific cell or range of cells in a different worksheet or workbook. This combination is useful for dynamically referencing cells and creating flexible formulas that can be easily updated. By using the INDIRECT function, the user can specify the location of the desired cell or range of cells, and the INDEX MATCH function will then retrieve the corresponding value from that location. This powerful combination can save time and effort in data analysis and management tasks in Excel.

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

=INDEX(INDIRECT("Table1["&$A11&"]"),MATCH(B$10,Table1[[Team]:[Team]],0))

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:

=INDEX(INDIRECT("Table1["&$A11&"]"),MATCH(B$10,Table1[[Team]:[Team]],0))

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:

x

Related terms:

  1. How can I use the INDIRECT function with the SUM function in Excel?
  2. To return a blank cell instead of #N/A when using the VLOOKUP function, you can add the IFERROR function to your formula. The syntax for IFERROR is: =IFERROR(value, value_if_error) In this case, the value would be the VLOOKUP function, and the value_if_error would be an empty quotation marks (“”). The updated formula would look like this: =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), “”) This will return a blank cell if the VLOOKUP function returns an error, such as #N/A. If the VLOOKUP function returns a valid result, it will be displayed as normal. Note: This solution will work for any type of error, not just #N/A. You can use it to return a blank cell for any type of error that may occur.
  3. How can I use the INDEX MATCH function to search across multiple columns in Excel?
  4. MENTAL COMBINATION
  5. Excel: Use INDIRECT with SUM
  6. How can I use the IF Function with INDEX MATCH in Google Sheets?
  7. How can I use INDEX MATCH to return the maximum value in Excel?
  8. How can I use INDEX MATCH from another sheet in Excel?
  9. Indirect Agonist
  10. How can I use the COUNTIF function in Excel to perform a partial text match?