HomeGoogle DocsSpreadsheetGoogle Sheets Indirect Function Examples and Usage

Google Sheets Indirect Function Examples and Usage

Published on

Below you can find few Google Sheets INDIRECT function examples. I have already another tutorial in the pipeline that will take you to the advanced part of it.

As the name suggests, Google Sheets INDIRECT function points indirectly to another cell or cells in the same tab or another tab within the Sheet. Without using the Indirect function, you can’t apply conditional formatting rules across the Sheet Tabs.

Syntax:

INDIRECT(cell_reference_as_string)

Here the “cell_reference_as_string” means a cell reference written as a string.

For example, A1 is a cell reference. If you enter this cell reference as “A1”, it’s called cell reference A1 ‘written as a string’.

You can replace the formula or say cell reference;

=A1

with

=INDIRECT("A1")

Both are the same. I hope, the below examples will give you enough idea about the practical use of this function.

Google Sheets INDIRECT Function Examples and How to Use

Indirect function in Google Sheets returns a cell reference specified by a string. I will explain it with 2-3 simple examples.

Indirect Function with Cell Address – How to

Here in this example, the value in Cell A1 is “Info Inspired”. Without directly referring to that cell, like =A1, you can indirectly do that.

indirect function 1

For example, you can refer to Cell Address A1 as =INDIRECT("A1")

Here “A1” inside the Indirect formula is a text string (cell_reference_as_string), not a cell reference. But the INDIRECT formula makes it a valid cell reference.

Alternatively, you can put the text A1 in any other cell, here for example in B4 (please see the above image), and use the formula as =INDIRECT(B4)

This is the key use of the Indirect. This time the cell reference B4 used within the Indirect function is not a string. What important is, the content in cell B4 is a cell reference but a text string. That is what you can see in the above screenshot.

In concise, you can either put the cell reference inside the function Indirect as a string (cell reference within double quotes) or enter a cell reference (without double quotes), but that cell must contain a cell reference.

Indirect Function with Range of Cells – How to

The below example will let you know how to use Google Sheets Indirect formula with a Range of Cells as well as the SUM function.

Indirect function with Sum

First, understand the formula in Cell E12 (see the formula bar on the screenshot).

Instead of simply using =sum(C2:C10) I have used that with Indirect. That’s just for making you understand how to use the Indirect function with other functions. But there is one more use.

Now I will tell you how to replace that formula with an alternative INDIRECT formula. You can put the cell range in another cell and refer to that cell.

For example, in Cell B12 you can enter the range like “C2:C10” that without quotes and refer Cell B12 in the formula as below.

=SUM(INDIRECT(B12))

You may ask why should you go for the Indirect when you can directly use the SUM function.

The answer is you must use Indirect in conditional formatting when you refer to another tab. Also, you can enter a tab name in a cell and range in another cell and refer to that tab and range using Indirect.

Just changing the tab name may help you to extract data from another tab with the same range. Without Indirect you may need to edit the formula to do that. I have explained that below.

Use of Indirect Function with Named Ranges – How to

Same as above, you can use the INDIRECT function in Google Sheets with Named Ranges.

If the name of the range of cells “C2:C10” is “ChequeAmount”, you can use the formula like below.

=sum(indirect("ChequeAmt"))

You can also use the Indirect formula as follows as a cell reference. See what is in cell D13. It is the name of the named ranges.

Google Sheets Indirect Formula with Named Ranges

That means you can Indirect Named Ranges in Google Sheets. In the below tutorials I have used this trick. Find time to read any of the below tutorials as the technique is almost the same.

  1. The Use of Named Ranges in Vlookup in Google Sheets.
  2. The Use of Named Ranges in Sumif in Google Sheets.
  3. How to Use Named Ranges in Query in Google Sheets.

Hope you are now familiar with how to use the Indirect function with Named Ranges in Google Doc Sheets.

Indirect Function with Sheet Tabs – How to

Using Indirect Formula with sheet names are also tricky. This is one of the real use of the Indirect function in Sheets. See the example below.

use of sheet names in indirect function

I am on the sheet “Master” and I called a cell value from Sheet “Company A” using indirect formula.

Hope you can understand the usage by referring to the screenshot above. If not I have covered it in another detailed tutorial.

Advanced use of the INDIRECT function in Google Sheets: View contents of all tabs in a single sheet without leaving your current tab – Tutorial using the Indirect function.

Indirect Function In Conditional Formatting – How to

Without the function Indirect, you can never do conditional formatting across the sheet tabs in Google Docs Sheets.

Keeping the importance of the Indirect function in conditional formatting in mind, I have written an elaborate tutorial covering that topic.

Must Read: Role of Indirect Function in Conditional Formatting in Google Sheets.

Prashanth KV
Prashanth KVhttps://infoinspired.com/about-me/
Introducing Prashanth KV: Your Trusted Google Sheets and Excel Guide Prashanth KV brings a wealth of experience in Google Sheets and Excel, cultivated through years of work with multinational corporations in Mumbai and Dubai. As a recognized Google Product Expert in Docs Editors, Prashanth shares his expertise through insightful blogging since 2012. Explore his blog for practical tips and guidance on maximizing your spreadsheet skills.

Creating a Running Balance with Dynamic Array Formulas in Excel

For calculating a running balance, we can utilize the SCAN function, which is a...

Comparing the FILTER Function in Excel and Google Sheets

I believe comparing the differences in how the FILTER functions work in Excel and...

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

More like this

Quartile IF in Google Sheets

To calculate quartiles based on conditions, referred to as "Quartile IF," we will use...

Drop Rows and Columns by Index Numbers in Google Sheets

With the help of two simple custom formulas, we can drop rows and columns...

Reference a Column by Field Label in Google Sheets QUERY

Here we'll explore the two best methods for referencing a column by its field...

20 COMMENTS

  1. Hello,

    I am searching for a formula that includes the INDIRECT function in the QUERY function because my data is on multiple sheets.

    Thank you.

  2. Thank you so much!

    Your answer works perfectly. I spent so many hours trying to research this, you saved me a ton of time and I can’t thank you enough! I have bookmarked this site and will be coming back to read all the articles about sheets.

    Again, thank you!

  3. Hello,

    I have been searching for an answer to an error I have come across and after reading many articles and reference items I was hoping you might be able to help me.

    I am trying to count the blank rows in a dynamic and partially filled column. I have a reference to the last filled Row at:

    'Formula Ref'!B3

    In another tab I am trying to reference the number in this cell to use as my end point to a range for the countblank function. No matter what I try I always get 0 as the answer when I know that the range I am searching has 276 blank cells. I am trying with this formula:

    =COUNTBLANK('Data - Sanatized'!H1:Indirect("H"&'Formula Ref'!B3))

    This works fine if I am not referencing a separate sheet in the opening part of my formula:

    =COUNTBLANK(H1:Indirect("H"&'Formula Ref'!B2))

    The above works correctly on the current sheet but when I try and make it reference the data sheet it fails to count.

    Any help would be GREATLY appreciated.

    • Hi, Jason,

      Here is the correct Indirect formula when you want to refer to a different sheet.

      =countblank(indirect("'Data – Sanatized'!H1:"&"H"&'Formula Ref'!B3))

  4. Hi,

    This formula gives an error.

    =arrayformula(HistoricalMM!INDIRECT(H1):BN)

    H1 = “BN”.

    BN dynamically changes. I want the latest.

    This works:

    =arrayformula(HistoricalMM!BN:BN)

    Thanks

  5. Hi there, I am stumped on a Google sheet problem and have read tons of blogs without any avail. It seems to me that it would be simple, but I haven’t found the right formula yet.

    What I am attempting to do is yield a sum value based on three criteria. However, the criteria changes per row. I’ve made it work generally with an array formula

    =ArrayFormula(sumif('Expense Tracker (NEW)'!$B$4:$B$150&'Expense Tracker (NEW)'!$D$4:$D$150&'Expense Tracker (NEW)'!$E$4:$E$150,"Jan"&"Operational"&"Mortgage ",'Expense Tracker (NEW)'!$K$4:$K$150))

    but I would rather the criteria indicators "Jan"&"Operational"&"Mortgage " be conditional on the value of a given cell vs. the exact text.

    This is for a financial tracking sheet where I input an expense in one tab (Expense Tracker (NEW)) and then categorize the expense using by selecting the date, type of expense (operational or project), and expense category.

    The intent is that on the second tab the formula will summarize the sum of values in the expense tracker depending if the month matches and the expense line item.

    Here is the link to the sheet I am working on: … link removed by admin …

    Any help or advice would be sincerely appreciated!

    • Hi, Cydney,

      No need to use INDIRECT in the formula. Also, when there are multiple conditions to consider in the SUM, the recommended function in your case is SUMIFS.

      Please see the new tab in your sheet named “info inspired”.

      I have inserted the below SUMIFS in P25 which then copied to its right and down.

      =sumifs('Expense Tracker (NEW)'!$K$4:$K,'Expense Tracker (NEW)'!$B$4:$B,P$4,'Expense Tracker (NEW)'!$D$4:$D,"Operational",'Expense Tracker (NEW)'!$E$4:$E,$A25)

      I hope, this helps.

    • Hi, Cydney,

      I think you are looking for an array formula. Then use the below SUMIF.

      =ArrayFormula(sumif('Expense Tracker (NEW)'!B4:B&'Expense Tracker (NEW)'!D4:D&'Expense Tracker (NEW)'!E4:E,P4:AA4&"Operational"&A25:A43,'Expense Tracker (NEW)'!K4:K))

      The same you can find in cell P24 in “info inspired 2”

  6. Thank you very much.

    I used the formula and this is what I experienced.

    When I paste it in K10, it gives #ref error. It works when I paste it in B10. But it is K10 (E10, H10, and so on) where it needs to work.

    Thanks.

  7. Sorry, I did not get any notification that you have posted.

    The formula that I use is copied from an article of yours. The link to my spreadsheet is – link removed by admin –

    I give comments in I, J, K2 merged columns.

    Thanks

    • Hi, SPH,

      No issue 🙂

      In my old posts, I have used RANDBETWEEN to shuffle rows.

      Now after the introduction of RANDARRAY, we can use an even shorter formula to randomize the range J10:J23 (we can make the range dynamic using the Indirect function later).

      =sort(J10:J23,randarray(14,1),0)

      Here is the more flexible version of the above same formula and we will use it.

      =sort(J10:J23,randarray(rows(J10:J23),1),0)

      Now the question is how to use Indirect to dynamically use the range J10:J23.

      The following formula in any column in row # 10 would return the range J10:J23 in text format (the active row number and the row number of the last value in column J).

      =address(row(),column(J1),4)&":"&
      SUBSTITUTE(ADDRESS(1,column(J1),4),1,"")&
      ArrayFormula(MATCH(2,1/(J:J<>""),1))

      Wrap this formula with Indirect and replace the range J10:J23 in the =sort(J10:J23,randarray(rows(J10:J23),1),0) formula twice.

      Here is the formula after the said modifications.

      =sort(indirect(ADDRESS(row(),column(J1),4)&":"&
      SUBSTITUTE(ADDRESS(1,column(J1),4),1,"")&ArrayFormula(MATCH(2,1/(J:J<>""),1))),
      randarray(rows(indirect(ADDRESS(row(),column(J1),4)&":"&
      SUBSTITUTE(ADDRESS(1,column(J1),4),1,"")&ArrayFormula(MATCH(2,1/(J:J<>"")
      ,1)))),1),0)

      You can feel free to remove the two ArrayFormulas and their corresponding open and close brackets from the above formula.

      Now you can use Index, Array_Constrain, Sortn, or Query to limit the output to 1. I’m using a Query.

      =query(sort(indirect(ADDRESS(row(),column(J1),4)&":"&
      SUBSTITUTE(ADDRESS(1,column(J1),4),1,"")&MATCH(2,1/(J:J<>""),1)),
      randarray(rows(indirect(ADDRESS(row(),column(J1),4)&":"&
      SUBSTITUTE(ADDRESS(1,column(J1),4),1,"")&MATCH(2,1/(J:J<>""),1))),1),0),
      "Select * where Col1 is not null limit "&indirect(K7))

      See if this helps?

    • Hi, SPH,

      It’s because the cell J9 in which you have an Indirect. Before my formula, that cell was returning blank and so I was unaware of it. After my formula in cell K10 it (cell J9 formula) returns a circular dependency error. I tested my formula in cell L10 so I haven’t notice that.

      Whenever you ask questions, the better practice is just to provide a demo sheet without any formulas. You have several formulas all over your sheet. It’s not possible for me to go through all the formulas.

      Thanks for your understanding.

  8. Hi,

    I use the following formula in google sheets.

    =ArrayFormula(Array_Constrain(vlookup(Query({ROW(D8:D23),
    randbetween(row(D8:D23)^0,9^9)},"Select Col1 order by Col2 Asc"),{row(D8:D23),
    D8:D23},2,FALSE),$D$3,1))

    I wish to replace d8:d23 with d8:indirect(a4). Normally indirect(a4) works, but in this case it gives an error.

    Each column has a different no. of filled rows and the formula has to adjust dynamically else I get a few blanks in my output when I use a fixed range.

    Thanks

    • Hi, SPH,

      It seems, your ArrayFormula without Indirect itself is not correct!

      This part is OK.

      Query({ROW(D8:D23),randbetween(row(D8:D23)^0,9^9)},"Select Col1 order by Col2 Asc")

      The Vlookup seems not as per the syntax.

      vlookup(Query({ROW(D8:D23),randbetween(row(D8:D23)^0,9^9)},"Select Col1 order by Col2 Asc"),{row(D8:D23),D8:D23},2,FALSE)

      If you explain the purpose of your formula or share with me an example sheet, then I may be able to help you with the formula and the Indirect function usage too.

LEAVE A REPLY

Please enter your comment!
Please enter your name here