Google Sheets Formulas
Menu
In this article, you will learn what the INDIRECT formula is and how to use it 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.
The general syntax is as follows:
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,
Examples #1 to 3 show how to call a value in a cell or a range on the same sheet.
Look at the following two examples below.
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.
Look at the following three examples.
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.
Check this article: Dependent Drop-down List in Google Sheets: Explained 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.
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.
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
Learn how to do this step-by-step in the video below 👇
