The INDIRECT function is powerful, but it doesn’t work with ArrayFormula to return results across multiple cells. Instead, you can use Lambda Helper Functions (LHFs) like MAP and REDUCE to achieve similar outcomes.
The LHFs streamline repetitive tasks and enhance the functionality of standard spreadsheet functions, and INDIRECT is no exception.
The application of the LHFs with INDIRECT varies based on your specific use case.
Which LHFs should I use with INDIRECT?
- MAP: To reference a cell in multiple sheets and retrieve them either individually or as an aggregate value. Alternatively, it can be used to refer to a range of cells in multiple sheets and retrieve aggregate values from each sheet separately.
- REDUCE: To refer to a range of cells in multiple sheets and stack them.
- Other LHFs: Functions like SCAN, BYCOL, and BYROW prove useful with the data extracted using REDUCE with INDIRECT.
Throughout this tutorial, we’ll explore various examples to enhance your understanding of these use cases.
When is it appropriate to use the INDIRECT ArrayFormula alternatives?
This distinction is crucial; it proves particularly useful in two scenarios:
- When you need to dynamically reference multiple sheet names stored in a row or column of cells.
- When you need to reference multiple named ranges indirectly. In this case, the names of the named ranges will be listed in a row or column.
Now, let’s delve into several examples illustrating the application of the INDIRECT ArrayFormula alternatives in Google Sheets.
MAP: An Alternative to ArrayFormula in INDIRECT for Google Sheets
In a Google Sheets file with four sheets (Dashboard, ABC 12345, ABC 12346, and ABC 12347), challenges may arise when indirectly extracting and aggregating data from multiple sheets.
Sheet names are listed in cells A3:A5 within the Dashboard sheet. In cell B3, we will employ INDIRECT ArrayFormula alternatives to either extract values or extract and aggregate values from the other sheets.
Here are solutions using the MAP function:
Problem 1: Extracting Totals
=MAP(A3:A5, LAMBDA(val, INDIRECT(val&"!B7"))) // Extracts the values in cell B7 from each sheet
In this and the following formula, A3:A5 constitutes the array containing sheet names, and B7 is the target cell in each sheet for extraction.
Problem 2: Extracting and Summing Totals
=SUM(MAP(A3:A5, LAMBDA(val, INDIRECT(val&"!B7")))) // returns 587.50 (198 + 196 + 193.5)Problem 3: Extracting and Aggregating Ranges
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val&"!B2:B6")))) // Returns the total of the range B2:B6 from each sheet separatelyIn this formula, A3:A5 represents the array containing sheet names, and B2:B6 is the target range in each sheet for extraction and aggregation. Essentially, it will return the result demonstrated in problem #1 above.
These MAP function examples address issues where ArrayFormula combined with INDIRECT falls short. The function iterates over specified sheet names, providing a dynamic solution for aggregating data from multiple sheets in Google Sheets.
REDUCE + INDIRECT: Enhancing Your Sheets Experience
When seeking an alternative to INDIRECT with ArrayFormula in Google Sheets, aiming for an array form of INDIRECT without ArrayFormula becomes essential due to its lack of support.
In the preceding example, we employed the INDIRECT function with MAP as a substitute for INDIRECT with ArrayFormula.
However, while MAP excels at fetching individual values from each sheet, we introduce the REDUCE function to address scenarios requiring the extraction and stacking of ranges.
How can you extract a range from each sheet and stack them either vertically or horizontally? Here’s an example:
For horizontal stacking:
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val&"!B2:B6")))) // Extracts and stacks the range B2:B6 from each sheet horizontallyFor vertical stacking:
=REDUCE(TOCOL(, 1), A3:A5, LAMBDA(a, val, VSTACK(a, INDIRECT(val&"!B2:B6")))) // Extracts and stacks the range B2:B6 from each sheet vertically
Both formulas iterate over sheet names in the array A3:A5, extracting the B2:B6 range. val represents the current sheet name in the array.
The REDUCE function stores intermediate results in a, the accumulator. TOROW/TOCOL represents the initial value in the accumulator (essentially null).
Explore this INDIRECT with ArrayFormula alternative further in the following tutorials:
- Combine Data Dynamically in Multiple Tabs Vertically in Google Sheets
- Dynamically Combine Multiple Sheets Horizontally in Google Sheets
INDIRECT with ArrayFormula Alternatives and Named Ranges
In the previous examples, we utilized an array of sheet names (A3:A5) within Lambda Helper Functions (LHFs) to iterate over each sheet name. Within the INDIRECT function, we combined cell/range references with the sheet name, as demonstrated by:
INDIRECT(val&"!B2:B6")for range referenceINDIRECT(val&"!B7")for cell reference
However, when dealing with named ranges, you should specify the range names instead of sheet names in A3:A5. In this case, within INDIRECT, you only need to reference the named ranges directly without combining cell/range references.
For instance, if the named ranges are vehicle_1, vehicle_2, and vehicle_3, with corresponding ranges ‘ABC 12345’!B2:B6, ‘ABC 12346’!B2:B6, and ‘ABC 12347’!B2:B6, respectively.
Extracting and Aggregating Ranges:
=MAP(A3:A5, LAMBDA(val, SUM(INDIRECT(val)))) // Returns the total of the values in each named range separatelyHorizontal Stacking:
=REDUCE(TOROW(, 1), A3:A5, LAMBDA(a, val, HSTACK(a, INDIRECT(val)))) // Stacks the indirect result of each named range horizontally
These formulas demonstrate the flexibility of INDIRECT with ArrayFormula alternatives, allowing seamless extraction and aggregation of named ranges in Google Sheets.
Resources
We have explored examples of INDIRECT with ArrayFormula alternatives to extract data from multiple sheets and named ranges, demonstrating their versatile applications. Below are a few tutorials that showcase the use of INDIRECT.
