In this article, we will learn how to use the INDIRECT function in google sheets.
INDIRECT Function simply returns the reference of a given cell or cell ranges. It takes the text or the cell reference as a mandatory input and returns the cell reference. There is a total of two arguments in this function, the second argument is not commonly used and it’s optional of course. So, let’s move to further reading then we will be learning this function practically.
Use Cases of INDIRECT Function in Google Sheets
When we have some data written in a spreadsheet file. The data is a kind of result data that can be changed daily weekly or monthly even yearly. The meaning is that the data has a chance to be changed in the future. We have a student who took the top position in the class. So, the next year the data has the probability to change because any other student can replace that kid anyone from the existing people, or any new student (from a different campus) can join them and can replace the student who took the top position.
This is the most common use case where the INDIRECT function is used. Now here as we have taken a cell reference where the name of the topper is written we have declared it in a heading and took his name from the cell reference. Now, what if someone replaces him? The data will become invalid. So, in this kind of problem we use the INDIRECT Function, we will see this example practically. I hope you have got the basic understanding and reason to learn how to use the INDIRECT function in google sheets.
How to Use INDIRECT Function in Google Sheets
Here we will learn how to use the INDIRECT function step-by-step with the help of some examples. Let’s first see the syntax and then we will move to examples and real-world applications of the INDIRECT function.
ref_text: can be any text or a cell reference (this is a mandatory argument)
ref_style: can be a Boolean value or a cell reference (this is an optional argument, and not used commonly) It is simply an answer to is-A1 notation.
A simple usage of INDIRECT function
You might be thinking, why do we need to use the INDIRECT function? There is no such need. Yes, after seeing the above example anyone will see this, but wait and let’s move to some real-world examples and you will see how this function helps us to face such problems.
Understanding the INDIRECT Function in Google Sheets
In this section, we will learn how to use the INDIRECT function in google sheets, and how many ways are there to use this function to refer to cell or range. Let’s see four possible ways to use it.
Ways to refer to a cell address
The first way
Here we simply used the classic method to call a cell reference using =A2 notation (INDIRECT Function not used)
The second way
Here we used the INDIRECT function and called a cell reference A4 =INDIRECT(“A4”), A4 inside the double quotes means that we are passing a string, thus the function will return the value in the cell A4 which is 10.
The third way.
Here we used the INDIRECT function and called a cell reference A4 =INDIRECT(A4), A4 without double quotes means that we are not passing a string. So, it will return a reference error, because the function could not find any cell reference in the given cell. Note that, when we pass the cell address within the double quotes so meant that give the value of the cell, and when we pass the cell reference without double quotes it means that return the cell address written in the cell address, I passed in the function. If there is any other text, string, formula, value, or even space, then the function will return a reference error because it could not find any reference in the specified cell.
The fourth way
Here we used the INDIRECT function and called a cell reference A6 =INDIRECT(A6), A6 is written without double quotes means that we are not passing a string. So, we need the cell reference’s value. Here I have some cell references written in cell A6 which is A5, then it will return the value of the A5 cell.
The first time it will go for reference, if found then it will return the value even if there is another cell reference written in the required cell.
The fifth way
This is the last method we can use to call cell reference, here I called it similarly to the previous method but here I had a chain of cell references to show you that the iteration is done only one time for the cell references, then it comes to the value only. Here I called a cell reference =INDIRECT(A7). I passed A7 without double quotes, which means that I am not looking for a direct value. Now in cell A7, I have a cell reference written which is A6, and the value in cell A6 is A5 which is another cell reference and the value in cell A5 is A, but the function returned A5 as the result. So now you have got a clear understanding that the lookup for the cell reference is done once, in the second iteration it looks for values and returns them.
I hope you have got a clear picture of the INDIRECT function in your mind; we will now see some examples that will solidify your understanding in a better way.
INDIRECT Function in Google Sheets – To Lock a Cell Reference
We have a list of some top-performing students, and the topper’s name is written on the top of the list, below we have a section for writing the topper’s name. We have simply referred to the cell E4 to get the value of it (Name of Student), in the next row we do the same thing using the INDIRECT function and passed the cell reference E4 inside double quotes, but both the values are the same. We will see and try to study the difference in both of the references when a change is made
Add a direct cell reference of the cell E4 below in the topper’s name column
Do the same in the next row using the INDIRECT method =INDIRECT(“E4”)
Both have the same result
Now assume you have a new student added (which means a new row is added) and replace the topper. Let’s see what happens next.
Add a new row to add a new topper name
The direct cell reference is updated as a row added and since the name of the topper is still the same
While the INDIRECT Function saw that a new row is added that’s why the values are changed. So, the function kept its cell reference as the old one but updates the value.
This is how you can lock cell references using the INDIRECT Function, and you have seen how helpful it is for us in this kind of situation where the data has a high chance of changes.
INDIRECT Function in Google Sheets – To Refer Cells from Different Sheets
In this section, we will learn how to use the INDIRECT function in google sheets to refer to cells from different sheets. We have three sheets that have different values for the same product. We will see how the INDIRECT function is useful in this case.
Sheet 1 data
Sheet 2 data
Sheet 3 data
In sheet 4 write the formula
The overall formula is
Hit Enter key and you’re done.
Note that the Sheet Name is coming from cell A2, so the sheet’s name should be the same as the row names.
Imagine if you need to do the same for 20 files. Now you see the INDIRECT method is very useful and makes it very easy to deal with such problems.
This is how the INDIRECT function makes it very easy to bring the values of each product price differs from the different sheets.
There are many more use cases of INDIRECT Function.
Its also used to dynamically refer to a named range and other ranges within the same workbook very smartly with a short syntax.
Download/Copy Google Sheets Practice Workbook
- When using the INDIRECT Function across different sheets, make sure that the sheet’s name should be written somewhere so you can use them as a cell reference.
- The INDIRECT function looks for the cell reference in the first iteration and the second iteration, and it returns the value of the cell reference written in the specified cell reference. It will not go more inside even if there is another cell reference written
- The INDIRECT function takes a cell reference as a string inside double quotes to return its value, and it can only receive a cell reference as a cell reference without double quotes when there is some other cell reference is already written in that particular cell reference
Wrapping up how to use the INDIRECT Function in google sheets, we have seen why the INDIRECT function is used, and how to use this function. We have seen some basic usages of the INDIRECT function and then we saw some practical implementations of the INDIRECT function that solves our problem.
I hope you find this article helpful and that you have now a better picture of the INDIRECT function in your mind. Now you can also say that this is one of the most underrated functions in google sheets. Having said that, I will see you next time with another helpful tutorial till then take care. Keep learning with Office Demy.