Arrow left
Back to product guides
Google Sheets Formulas

INDIRECT Function in Google Sheets: Explained

In this article, you will learn what the INDIRECT formula is and how to use it in Google Sheets.

What does the INDIRECT formula do in Google Sheets?

The formula returns a value(s) in a cell or range specified in the formula. For example, if you type in “=INDIRECT(A2)”, it returns a value in cell A2. If you insert “=INDIRECT(A2:A4)”, it calls the values in the particular range. You can use this formula as a selected range, too. For insurance, you can create a formula like “=AVERAGE(INDIRECT(A2:A4))”, which gives you the average of the values in the range of cells A2 to A4, though you don’t necessarily do this in this simple case. Lastly, you can use Named Ranges in the formula to show the range or refer to it in another formula, as shown in the example above (the INDIRECT function used in the AVERAGE formula). In summary, this function is beneficial when you want to switch the cell or range referred to by just typing in the cell indexes, not by selecting them manually.

How to use the INDIRECT function in Google Sheets

  1. Type “=INDIRECT” or navigate to the “Insert” tab (or “Functions” icon) → “Function”“Lookup”“INDIRECT”.
  2. Insert a cell or range referred by cell reference or manual input with quotation marks such as “B2”.
  3. Press the “Enter” key.

The general syntax is as follows:

=INDIRECT(cell_reference_as_string, [is_A1_notation])

Cell_reference_as_string: You can type in a cell or range’s indexes manually or use a cell reference in which you refer to a cell containing a cell or range’s indexes. When you use a cell reference, you don’t need to put quotation marks to the value in the referred cell. In case of manual input, don’t forget to enclose it with quotation marks.

Is_A1_notation (Optional): In this argument, you can insert “TRUE” or “FALSE” or keep it blank. If it’s blank, it is considered “TRUE” by default. This parameter is for the formula to recognize the cell reference properly. If it is “TRUE”, the function finds it the “A1” method of input, and if it is “FALSE”, it considers it the “R1C1” type of input. (A1 is equivalent to R1C1, meaning Row1Column1). 

Note: If you input a range in “cell_reference_as_string”, the formula outputs the range automatically, so ensure you give enough space for the function to spread the result.

Look at the basic examples below to understand the formula and how to use it better. Note the “is_A1_notation” argument is blank in all INDIRECT functions in this article,

How to use the INDIRECT formula in Google Sheets with examples

Examples #1 to 3 show how to call a value in a cell or a range on the same sheet. 

How to refer to a cell or range in a different worksheet in the same file

Look at the following two examples below.

How to pull data from another worksheet with the INDIRECT function in Google Sheets

Examples #4 and 5: These two cases present examples of pulling data from another sheet in the same file, filling the first argument by manual input (#4) or cell reference (#5). ‘62’!B5:C5 means a range of B5:C5 on the sheet named 62. The structure is ‘sheet name’ (sheet name enclosed by apostrophes), and a range is connected with !, an exclamation mark - ‘sheet name’+!+(a cell / a range). Also, even when you refer to another worksheet, you need to add quotation marks in case of manual input.

How to use the INDIRECT function with Named Ranges or other functions

Look at the following three examples.

How to use the INDIRECT formula with the Named Ranges or other functions

Examples #6 and #7 show how you can use named ranges in the INDIRECT formula. “LotSize” represents a range of D4:D8 on the sheet. Note you need to add quotation marks when you manually input the range's name. As you can see, the formula return values across the cells in the same direction as the original range.

Examples #8: This is the example of combining the INDIRECT function with another formula and Named Range. When you use the INDIRECT formula in another formula as a range, you don’t need to put quotation marks at the beginning and the end of the INDIRECT function.

How do you use indirect validation in Google Sheets?

Check this article if you are looking for a way to create a dependent drop-down list whose choices change based on your choice in the first pull-down menu.

Other formulas related to lookup

If you don’t get the right answer to your question in this article or you have other questions on lookup formulas, you may find the answers in the following articles.

How to Use VLOOKUP in Google Sheets

How to Use HLOOKUP in Google Sheets

XLOOKUP - Google Sheets: Explained

How to Use INDEX Function in Google Sheets

How to Use MATCH Function in Google Sheets

XMATCH in Google Sheets: Explained

OFFSET Function in Google Sheets: Explained

Analyze your live financial data in a snap

Are you learning this formula to build a financial model or financial KPI dashboard, or conduct financial analysis? If so, LiveFlow may help you to automate manual workflows and keep numbers updated in real-time. For instance, you can easily compare your Budget versus Actual without any manual work. Furthermore, you can access other templates here, from the simple Income Statement and Operational KPI templates to Multi-Currency Consolidated Income Statement, Balance Sheet, and Cash Flow templates. You can also customize these templates as you want without breaking the automated data inflow.

To learn more about LiveFlow, book a demo.

Automate any custom financial dashboard in Google Sheets with LiveFlow

Learn how to do this step-by-step in the video below 👇

Do you need personal help?

Our team of real people are here to help you any time between 9am and 10pm GMT.
Check Icon
Email us at:
Check Icon
Call us at +1 (415) 650-1711