Indirect in Google Sheets

What Is Indirect in Google Sheets?

The Google Sheets INDIRECT formula returns a reference to a given range. The INDIRECT function takes in the cell address in text form and returns a cell reference. It works oppositely to the ADDRESS function, which returns an address in text format.

For example, if you have the value of 23 in cell A1. You can use the following formula to get the same value in cell A2. Thus, the function allows you to use text strings to refer to cell references dynamically. It is beneficial when you want to create flexible formulas based on the content of other cells.

=INDIRECT(A1).

Indirect-in-Google-Sheets-Definition
Key Takeaways
  1. INDIRECT in Google Sheets allows you to use text strings to dynamically refer to cell references. For example, if a value of 10 exists in cell A1, entering =INDIRECT(“A1”) in cell A2, gives you the value 10 in A2.
  2. The syntax of the INDIRECT function is as follows:

INDIRECT(reference_string, [is_A1_notation]). Here, the first argument specifies the cell reference or the range. The second argument is a Boolean value, TRUE or FALSE, which indicates either A1-style reference or R1C1-style reference, respectively.

  • It can be used for dynamic cell or range reference as well as with named ranges. For example, for a named range “Cost,” you can find the total cost using the formula

=SUM(INDIRECT(“Cost”)).

  • If the reference_string argument is invalid or does not exist, INDIRECT in Google Sheets will return a #REF! error.

Syntax

The syntax for the INDIRECT in Google Sheets function is as follows:

=INDIRECT(ref_str,[is_A1_notation])

There are two parameters in the INDIRECT in Google sheets formula:

  • ref_strt: This is a text string containing a reference to a cell or range of cells
  • is_A1_notation: (Optional) We specify TRUE or FALSE. A TRUE value indicates that the ref_text is specified in the “A1” notation, while a FALSE value indicates that it is specified in the “R1C1” notation. Default value is TRUE.

A1 vs. R1C1 Notation

R1C1 reference

We can reference a cell in Google Sheets called the R1C1 notation. It refers to a cell or range reference using a row number and column number instead of the letter-number format. Here, R refers to the Row, and C refers to the column. For instance, instead of cell A1, you refer to that cell as R1C1 (row 1, Column 1). Cell B6 is referred to as R6C2.

R1C1 references can also be absolute or relative. In R1C1, when you refer to a cell, an address is created for the referred cell with its distance from the active cell.

Here, let us look at how R1C1 references can be mentioned.

A1-vs.-R1C1-Notation

When we want to reference it as a relative reference, we use the square brackets. A value represented as R[-3]C[2] refers to a cell that is behind by three rows and two columns to the right.

The R1C1 notation can also reference ranges where two cell references are joined with a “:” colon.

For example, R[-2]C[0]:R[-1]C[-1] refers to the Google Sheets INDIRECT range shown in green in the figure below. Here, R5C5 is the active cell.

A1-vs.-R1C1-Notation-1

How To Use InDirect in Google Sheets?

Using INDIRECT in Google Sheets can enhance the flexibility of your formulas. Using it in Google spreadsheets involves a few steps, which we can observe below.

Enter the function manually

Step 1: Open your Google Sheets document and click on the cell where you want to use the INDIRECT function. Now, type =INDIRECT(.

Enter-the-function-manually-Step-1

Step 2: Enter the cell whose reference you want to use. Here, we enter A1. It should be entered as a string within quotes. Close the braces.

Enter-the-function-manually-Step-2

Step 3: Press Enter. We get the value present in the specified reference in cell B2.

Enter-the-function-manually-Step-3

Through the Google Menu Bar

Go to the “Insert” tab – “Function” – “Lookup” – “INDIRECT.”

Through-the-Google-Menu-Bar-1

Insert a cell or range referred by cell reference or enter a reference with quotation marks such as “F2”. Press Enter.

Examples

Let’s now look at some of the examples to understand more about exporting data from Tableau to Excel.

Example #1 – INDIRECT Function with Dynamic Named Ranges

Using the Google Sheets INDIRECT Function to Refer to a Dynamic Named Range

One of the best applications of the INDIRECT function in Google Sheets is to refer to a dynamic range.

For example, we have the marks of 3 students in Math and Science as shown below.

Indirect-in-Google-Sheets-Example-1

Step 1: We must display the total obtained in any subject we enter in cell B6. To do this, we create named ranges, which will make our job easier.  

Let us create a named range for Math. Select the cells under Math.

Example-1-Step-1

Step 2: To create a named range for the Math, select “Data” –Named Range.

Example-1-Step-2

Step 3: It will open the “Named Ranges sidebar at the right. Type “Math” in the input box above the cell range. Click “Done.You get a named range for the group of cells under “Math.

Example-1-Step-3

Step 4: Create a named range for Science.

Example-1-Step-4

Step 5: Select cell “C6” and type the following formula in it

=SUM(INDIRECT(“Math”)).

Example-1-Step-5

Step 6: Press Enter. The sum of the marks will appear under the range “Math.”

Example-1-Step-6

Explanation

Here, the INDIRECT function used the named range “Math” as its parameter.

If the text “Math” is used, the INDIRECT function returns a reference to the named range, “Math.” The SUM function will take the numeric values under it and return the sum.

Example #2 – INDIRECT for Cross-Reference in Other sheets

Another way to use INDIRECT in Google Sheets is when you have to take data from multiple sheets. Here, the INDIRECT function uses the reference as a string from multiple sheets. In the current example, we have data from two stores selling gadgets in Chicago and Dallas. We name the sheets the same way as well.

Indirect-in-Google-Sheets-Example-2

Step 1: If you have two sheets named ‘Dallas’ and ‘Chicago’, as shown above.  In another sheet, we must display the number of laptops sold in both Chicago and Dallas.  Let us name this sheet as Total.

Example-2-Step-1

Step 2: Conventionally, we type the name of each sheet along with the required cell reference, which can be time-consuming and prone to mistakes if there are many such sheets. Let us look at a simpler way to do it.

In cell C2 of the new sheet, type the following formula:

= =SUM(INDIRECT(“Chicago” & “!B2”),INDIRECT(“Dallas” & “!B2”))0

Press Enter. Here,

  1. We have to find the sum of the two laptop numbers in the two sheets, Chicago and Dallas.
  2. The first INDIRECT function, INDIRECT(“Chicago” & “!B2”), mentions the cell we are referring to and the sheet name.
  3. The second INDIRECT function, INDIRECT(“Dallas” & “!B2”)), again refers to the Dallas sheet and the cell whose reference we want.
  4. We combine both to get their sum using the SUM function.
Example-2-Step-2

If you need data from different sheets, you can have a separate column containing the cell references for the sheets and use the INDIRECT function.

Example #3 – Combine VLOOKUP with INDIRECT

This is another example of how to use the INDIRECT function to work with multiple sheets.

By combining VLOOKUP and INDIRECT, you can look up data from different sheets based on your requirements. Here, we have a sheet called Details, which contains some details of a few employees.

Indirect-in-Google-Sheets-Example-3

Step 1: In another sheet, we have the name “Details” in cell B1. Enter the following VLOOKUP + INDIRECT formula in cell B3.

=VLOOKUP(“Lucy”, INDIRECT(B1 & “!A2:C6”),3,FALSE)

Explanation:

  • Here, we are looking to retrieve the salary of an employee, Lucy, if she exists in the table.
  • The INDIRECT function refers to cell B1, which contains the sheet name and the range where the lookup is to be deployed on that sheet (A2:C6).
  • We give the index three as the salary is located in the third column, and FALSE means an exact match.
Example-3-Step-1

Step 2: Press Enter. You can use INDIRECT in Google Sheets to get the value of employee Lucy’s salary in your sheet.

Example-3-Step-2

Example #4 – VLOOKUP with INDIRECT – #N/A Error

As a continuation of the above example, we got the salary of employee Lucy. Let us look at what happens if the employee’s name is not present in the database.

Step 1: Enter the formula below in cell B3. Press Enter.

=VLOOKUP(“Joey”, INDIRECT(B1 & “!A2:C6”),3,FALSE)

Example-4-Step-1

Step 2: As you can see, you get an #N/A error. We can handle this error using the IFERROR formula. Modify the above formula as shown below.

=IFERROR(VLOOKUP(“Joey”, INDIRECT(B1 & “!A2:C6”),3,FALSE), “Name n0ot Found”)

Example-4-Step-2

Step 3: Press Enter. You get the result shown below.

Example-4-Step-3

Important Things to Note

  1. The INDIRECT function helps you lock cell references. So, you can refer to a fixed cell location rather than the value in the cell location, which is very useful during dynamic updates.
  2. When using INDIRECT in Google Sheets, you can use the familiar A1 style notation or the R1C1 notation, which marks a cell using a row and column number.
  3. The INDIRECT function, along with the ROW function, can be used to create column vectors.

Frequently Asked Questions (FAQs)

1. How do you use the INDIRECT Function in VLOOKUP?

One of the powerful features of the INDIRECT function is that you can combine it with the VLOOKUP function. Thus, you can use the INDIRECT function with multiple sheets since using VLOOKUP with INDIRECT allows you to use the lookup data from multiple sheets.
For example, =VLOOKUP(C2,INDIRECT(B2 & “!A1:B5”),3,FALSE) is used to retrieve a value from the third column in a sheet whose name has been mentioned in cell B2.
By combining VLOOKUP and INDIRECT, you can create dynamic references that are adaptable to changes in the sheet names.

2. What are A1 and R1C1 notations in INDIRECT in Google Sheets?

When using the INDIRECT function, we can use two types of cell notations. The first is the A1 Notation, which we regularly use. It uses letters for columns and numbers for rows. For example, B5 refers to Column B and row 5. The R1C1 Notation uses numbers for both rows and columns. For example, we refer to cell A1 as R1C1, A2 as R2C1, and so on.

3. What errors do you encounter when using INDIRECT in Google Sheets?

When we use INDIRECT, if the cell or range specified as an argument does not exist, INDIRECT will return a #REF! error. Thus, you can cross-check and apply the right range or reference. You also get the #N/A! error when using INDIRECT with VLOOKUP, and the value is not found. In such cases, you use the IFERROR function to fix this.

Download Template

This article must help understand Indirect in Google Sheets with formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to What Is Indirect in Google Sheets. We learn Indirect in Google Sheets, its examples, working template. You can learn more from the following articles.

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *