1. INDIRECT Function
1.1 Dynamic References
Most of our data sets are dynamic, and we want the changes in range size or sheets reflected in our formulas. While the former is easier to implement, the latter is more challenging because most functions are dependent on cells and not the sheets. There are a lot of ways to construct dynamic references, and one of them is using the function INDIRECT.
1.2 What does the INDIRECT do in Google Sheets?
The INDIRECT function returns a reference from a given reference in text format. This may not make sense at first read, but that is literally what the INDIRECT function does. Instead of writing the reference directly in the formula, we can write it as a text input in the INDIRECT function, which will return a valid reference. Both do the same thing (Fig. 1.1), but the INDIRECT function does it in an indirect way. The important keyword here is “text,” and this makes all the difference.
Figure 1.1 Direct Reference vs. INDIRECT
1.3 INDIRECT Syntax
Below is the syntax for INDIRECT:
There are two inputs in the INDIRECT function: cell_reference_as_string (text reference) and is_A1_notation (notation). The text reference is the valid reference in text format. The notation is a boolean input, which sets the type of notation that is expected in the text reference. It’s an optional input with a default value of TRUE, which means that the reference is in A1 notation (Column Letter + Row Number). A FALSE input means the reference is in R1C1 notation (Row Number + Column Number). The former notation is the most common usage, and therefore, the notation is omitted most of the time (When optional inputs are omitted, they take their default values). Let’s take a look back at the basic example in Fig. 1.1 to help us build an intuition on how the formula works.
B2 and C2 are both linked to A2, but they differ in the method of referencing. C2 utilizes the INDIRECT function. As shown in the figure above, we need to input a valid reference in text format, and the INDIRECT function converts the text reference into a valid reference that will return a value. The notation is omitted, and therefore, it will take its default value, which is TRUE. If notation is set to FALSE, the function will expect the R1C1 notation. If the notation of the text reference is different from the set notation, the INDIRECT function will return an error (Fig. 1.2).
Figure 1.2 Invalid Notation
- Breaking Down the Formula
Figure 2.1 INDIRECT Function
In this section, we’ll talk about the rules and criteria for each INDIRECT input, how to write them, how INDIRECT processes these inputs and how the inputs affect the output. We’ll build an intuition to make it easier for us to visualize and create an INDIRECT formula for your problems.
2.1 INDIRECT Input/Arguments
2.1.1 Cell_Reference_As_String
Before we move on to the rules and properties, let’s discuss first the two types of notations that can be used in the INDIRECT function: A1 notation and R1C1 notation. Note that the letters in the notations are always uppercase letters.
- A1 Notation
The A1 notation is the common notation that we used in spreadsheets and is based on the column (letters) and row (numbers) headers of the spreadsheet (e.g., A1, B1, C1).
- R1C1 Notation
This notation is based on the row and column numbers. The equivalent R1C1 of A2 in Fig. 1.2 is R2C1 (row 2, column 1) (Fig. 2.2).
Figure 2.2 R1C1 Notation
We can also create relative references using the R1C1 notation. The destination cell of the INDIRECT function will be the reference cell, which is indexed as 0. Starting from C2 (start count from 0), A2 is 2 cells to the left, which is equivalent to -2 (Fig. 2.3). They have the same row numbers, and therefore, there’s no offset, which means 0. (Note: positive right and down, negative left and up)
Figure 2.3 R1C1: Relative Reference (Square Brackets)
The R1C1 notation is an uncommon notation because it’s quite inconvenient to construct INDIRECT formulas using it. The column headers are letters, and therefore, to get the column number, you need to count it, which makes it inconvenient. For A1 notation, we don’t need to count or think about the cell reference.It’s important to note here that the text reference can be a single cell or a range of cells.
Here are the important rules that you need to consider for the text reference:
2.1.1.1 Syntax and Location of the Text Reference
In Google Sheets, the text reference can be written or coded in four ways depending on the scenario:
- Reference to a cell:
We can store the text reference in a cell and reference that cell (Fig. 2.4). The reference in the example below is stored in B2. We used B2 directly in the INDIRECT function.
Figure 2.4 Text Reference stored in a cell B2
Also, it’s important to know that we can refer to the cells from any sheet. This means that the cells can be:
- in the same sheet with the function
- in another sheet within the same workbook
- Or in another sheet in another workbook (using ImportRange function)
- A Constant value:
We can write the text reference in the formula as shown in the previous examples. Make sure that the text reference is enclosed with quotation marks.
=INDIRECT(“A2”)
- Named Range
We can also write named ranges as text reference for the INDIRECT function (Fig. 2.5). The “Input” named range in the example below refers to A2. We can create named ranges through Data -> Named Ranges. We can set the notation to TRUE or FALSE for named ranges.
Figure 2.5 Named Range
- Text Reference from a Nested Function
We can also nest functions inside the INDIRECT function. Most of the time, we concatenate functions to complete a reference just like the example below:
=INDIRECT(“A”&MATCH(E2,A1:A10,0))
2.1.1.2 Dimension and Size of Text Reference
In spreadsheets, the dimension is related to rows and the columns. If our data is written in one column but multiple rows or one row but multiple columns, the dimension of our data is one-dimensional (1D). If our data is in a table with multiple rows and columns, then the dimension is two dimensional (2D).
The rows and columns serve as coordinates of the cells within the spreadsheet. For 1D data, we only need to specify one coordinate (row or column) to know where the value is located. For 2D, we need to define the row and column to locate the value.
The size of the data is related to the number of rows and columns, which also translate to the number of entries.
The INDIRECT function can’t evaluate array operations, and therefore, referencing a range of cells that contains a list of text references won’t work (Fig. 2.6). We can still input the 1D vertical list (B2:B4) in the formula, but it will only evaluate the first cell in the list, which in this case is B2.
Figure 2.6 List of Text References (B2:B4)
2.1.1.3 Data Type of Text Reference
The text reference must always be in text format.
2.1.2 Is_A1_Notation
The notation is boolean input. It can only accept TRUE or FALSE values or their equivalent numerical values, which are 1 and 0, respectively. The notation can also be a cell reference, where the boolean value is stored, and just like the text reference, it can’t accept an array.
2.2 The INDIRECT Process
2.2.1 How does the INDIRECT work?
We already had a glimpse of how it works from section 1.3, and in this section we’ll look at more detailed examples of how the INDIRECT processes its inputs.
2.2.1.1 Text Reference = Range of Cells
We’ve already seen basic examples of the INDIRECT function, and it’s a very straight forward function. It converts the text reference to a valid reference and returns the corresponding value from that cell reference. Now, let’s look at the scenario where the text reference is a range of cells (Fig. 2.7).
The text reference in the example below is a 1D vertical list (A2:A4). The INDIRECT function will return an array containing the values of A2:A4 with the same dimension, size and arrangements.
Figure 2.7 Text Reference = Range of Cells
2.2.1.2 Is INDIRECT a Volatile Function
Volatile functions (e.g., NOW, TODAY) are functions that recalculate every time the spreadsheet recalculates. This means that even if there are no changes in the arguments or inputs of the volatile functions, the volatile functions will be triggered as long as there are calculations in the spreadsheet. This slows down the spreadsheet. Therefore, volatile functions are usually not advisable in large spreadsheets.
In Excel, the INDIRECT function is a volatile function, but in Google Sheets, it’s not a volatile function. Therefore, we can utilize its power without reservation.
2.3 INDIRECT Output
2.3.1 Dimension and Size of the Output
We’ve seen in section 2.2.1.1 that the dimension, size and arrangement of the output of the INDIRECT function will be dependent on the same properties of the text reference. For array results, just make sure that the destination cells of the outputs are vacant cells. If there are values that are occupying the cells where the results should be displayed, then an error will be returned instead of the results (Fig. 2.8).
Figure 2.8 The INDIRECT function returns a #REF error due to B3 blocking the results
2.3.2 Data Type of the Output
The data type of the output will be the same as with the data type of the values from the returned references.
- Applications of INDIRECT
In this section, we’ll tackle how to formulate your own INDIRECT for different scenarios. Some complicated tasks will require some tricks, but basically it all boils down to the rules of the INDIRECT function. We just need to follow the rules, and we’ll be able to create the INDIRECT formula that we want.
3.1 Using INDIRECT to perform Lookup
We can use the INDIRECT function to perform a lookup. Let’s look at the example below (Fig. 3.1). The goal is to extract the amount of the given lookup value in D2. The amount will be extracted from Column B, and therefore, we set the column letter to B. For the row number, we use the MATCH function. The MATCH function returns the position of a value within a list. It has three inputs. The first input is the lookup value. The second input is the list where the lookup value is searched. The last input is the type of match. We set it to 0, which means exact match. If there’s a match in Column A, the MATCH function will return the position. If there’s no match, it will return an error.
In the case below, the MATCH function will return 4 because ZZ-1001 is the 4th entry in A1:A6. The resulting text reference will be B4, and therefore, the value of B4 is returned, which is 1,000.
Figure 3.1 Lookup using INDIRECT
We can also perform a two-way lookup using the INDIRECT function. There are two lookup values for the example below (Fig. 3.2). The Code lookup corresponds to the row coordinate while the Month lookup corresponds to the column coordinate. We use the R1C1 notation in combination with the MATCH function. The two MATCH functions will return the row and column coordinates that will be concatenated to the R and C, respectively. We can also use the ADDRESS function to replace the R1C1 notation:
=INDIRECT(ADDRESS(MATCH(E2,A1:A6,0),MATCH(F2,A1:C1,0)))
` The ADDRESS function requires at least the row and column coordinates and returns the cell reference in text format, which is perfect for the INDIRECT function.
Figure 3.2 Two-Way Lookup using R1C1 Notation
3.2 Using INDIRECT to Create Static References
Another use of the INDIRECT function is to convert a reference to a static reference that will not change even if there are addition and removal of rows and columns. Any changes in the columns and rows in the spreadsheet will affect the references in formulas. Therefore, to prevent any changes due to addition and removal of rows or columns, we can use the INDIRECT function to fix the reference.
The data set (A2:A4) in the example in Fig. 3.3 is a dynamic data set. Rows are added above A2 for new subs, and are sorted in ascending order based on the amount. Therefore, cell A2 will always be the least Sub. Both the INDIRECT function and the direct reference are shown in the figure. Initially, both returned the same result. After the addition of a new row (Sub 4) and sorting of the data, Sub 4 is now at A2 since it has the least amount. The INDIRECT function returns Sub 4 while the direct reference is adjusted from A2 to A3 due to the addition of the new row.
Since the reference in the INDIRECT function is in text format, it’s not affected by any changes in row and column sizes. Therefore, the INDIRECT function can be used to convert references to static references.
Figure 3.3 Static Reference using INDIRECT
3.3 Using INDIRECT with Dynamic Sheet Reference
The task in the example below is to extract the total amount for the given month in A2 of the Master Sheet (Fig. 3.4). The data for each of the months is stored in separate sheets as shown in the figure. Therefore, A2 also corresponds to the name of a sheet. Sheet references are always written before the cell reference. It starts with the name of the sheet followed by an exclamation mark, which works as the separator between the sheet reference and cell reference. If the given month is changed, the INDIRECT function will automatically update and extract the corresponding value for that month.
Figure 3.4 INDIRECT: Dynamic Sheet Reference
This simple example above can be extended to other data extraction scenarios such as VLOOKUP and INDEX-MATCH formulas. If the reference sheets vary, we can use the INDIRECT function to replace the references for the data inputs. The formulas below are some of the solutions for the extraction problem in Fig. 3.5.
=VLOOKUP(A2,INDIRECT(B2&”!A2:B5″),2,FALSE) = VLOOKUP(A2,Jan!A2:B5,2,FALSE)
=INDEX(INDIRECT(B2&”!B2:B5″),MATCH(A2,INDIRECT(B2&”!A2:A5″),0)) = INDEX(Jan!B2:B5,MATCH(A2,Jan!A2:A5,0))
=INDIRECT(B2&”!B”&MATCH(A2,INDIRECT(B2&”!A1:A5″),0)) = INDIRECT(“Jan!B”&MATCH(A2,Jan!A1:A5,0))
Figure 3.5 The data sets are contained in difference month sheets while the output is in the Master Sheet.
3.6 Using INDIRECT with Dynamic Range Size
Most of the time, the size of our data sets can vary. We can use the INDIRECT function to create dynamic cell references.
The example below contains a data set that can increase or decrease in row size (Fig. 3.6). To accommodate the changes in row size, we use the INDIRECT function with the COUNTA function. The COUNTA function will return a total count of cells that contain values in Column A. This count is equivalent to the row size of the data set assuming that there are no blank cells in between the data in Column A.
Figure 3.6 Dynamic Row Size
3.5 Using INDIRECT to Generate Arrays
The INDIRECT function can also be used to generate a number sequence. We can create a vertical list (Fig. 3.7) or a horizontal list (Fig. 3.8). The INDIRECT function will return the row or column references. The ROW and COLUMN functions will return the corresponding row and column numbers, respectively, based on the given references. Both functions can only accept a single cell reference. Therefore, we need to use the ArrayFormula function to evaluate array inputs.
Figure 3.7 Vertical List
Figure 3.8 Horizontal List