How to use the INDIRECT function to define function arrays

In Excel, we can use the INDIRECT function to define arrays that are arguments for other functions, provided that those arrays are referencing the current workbook.

 

Consider the following example:

We want to retrieve values from the sheets adjacent to Sheet1, but still in the same workbook. Instead of writing a specific formula for every sheet, we will write a single formula that will link the data from all of the worksheets.

 

We can retrieve our data using the INDEX MATCH MATCH combination:

There are two versions of this formula shown here.

In the first version, we are directly referring to the OPEX sheet.

In the second version, we are generating a reference to the OPEX sheet using the INDIRECT function. The name of the sheet can be seen mapped in cell A5. That name is enclosed in apostrophes ‘ and an exclamation mark ! is added – this is a way to ensure that Excel will correctly process all generated sheet names. Finally, the INDIRECT function converts that sting to a valid array reference.

The second version, which uses the INDIRECT function, is more complicated. However, it can be far easier reused. We can use the same formula to retrieve all of the values we are interested in:

In our example, the formula in cell C2 is the same as the formula used in cell F5, which is exactly the point.

 

The same result, but achieved using the XLOOKUP function instead of the INDEX MATCH combination, will look like this:

 

The same result, but achieved using the FILTER function, will look like this:

 

What about SUMIFS and other IFSs functions that use cell ranges?

It is both simple and complicated.

Are we always trying to retrieve values from the same column in different sheets? If yes, great. We can combine SUMIFS with INDIRECT in the following way:

=SUMIFS(

INDIRECT(“‘”&$A5&”‘!D:D”);

INDIRECT(“‘”&$A5&”‘!A:A”);

$B5)

We will retrieve values from column D from any specified sheet.

However, we will not be able to reuse this formula for all of the columns we are trying to fill. For that, we will often need to dynamically define the column we are retrieving the value from.

In order to dynamically define the sum range in the SUMIFS function, typically, INDEX MATCH or XLOOKUP needs to be used. When INDIRECT function is in play, the easiest way to accomplish this is to use the ADDRESS and MATCH functions:

Easiest may not be the correct term here, and SUM FILTER combination can achieve the same result in a simpler way.

 

Dig deeper:

INDIRECT function

Combining INDIRECT with ADDRESS