How to Use INDIRECT Function in Google Sheets [Complete Guide]

how to Use the INDIRECT Function in Google Sheets 15

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.

See also  How to Apply Conditional Formatting Lowest and Highest Value 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.

Syntax

=INDIRECT(ref_text,[ref_style])

Here,

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

how to Use the INDIRECT Function in Google Sheets 1

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.

Step 1

Ways to refer to a cell address

how to Use the INDIRECT Function in Google Sheets 2

Step 2

The first way

Here we simply used the classic method to call a cell reference using =A2 notation (INDIRECT Function not used)

how to Use the INDIRECT Function in Google Sheets 3

Step 3

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.

how to Use the INDIRECT Function in Google Sheets 4

Step 4

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.

how to Use the INDIRECT Function in Google Sheets 5

Step 5

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.

how to Use the INDIRECT Function in Google Sheets 6

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.

Step 6

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.

how to Use the INDIRECT Function in Google Sheets 7

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.

See also  How to Strikethrough on Google Sheets [4 Methods]

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

Step 1

Sample data

how to Use the INDIRECT Function in Google Sheets 8

Step 2

Add a direct cell reference of the cell E4 below in the topper’s name column

how to Use the INDIRECT Function in Google Sheets 9

how to Use the INDIRECT Function in Google Sheets 10

Step 3

Do the same in the next row using the INDIRECT method =INDIRECT(“E4”)

how to Use the INDIRECT Function in Google Sheets 11

how to Use the INDIRECT Function in Google Sheets 12

Step 4

Both have the same result

how to Use the INDIRECT Function in Google Sheets 13

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.

Step 5

Add a new row to add a new topper name

how to Use the INDIRECT Function in Google Sheets 14

how to Use the INDIRECT Function in Google Sheets 15

Step 6

The direct cell reference is updated as a row added and since the name of the topper is still the same

how to Use the INDIRECT Function in Google Sheets 16

Step 7

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.

how to Use the INDIRECT Function in Google Sheets 17

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.

See also  How to Insert Check Mark in Google Sheets [3 Methods]

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.

Step 1

Sheet 1 data

how to Use the INDIRECT Function in Google Sheets 18

Step 2

Sheet 2 data

how to Use the INDIRECT Function in Google Sheets 19

Step 3

Sheet 3 data

how to Use the INDIRECT Function in Google Sheets 20

Step 4

In sheet 4 write the formula

how to Use the INDIRECT Function in Google Sheets 21

Step 5

The overall formula is

=INDIRECT(A2&”!B2″)

how to Use the INDIRECT Function in Google Sheets 22

Step 6

Hit Enter key and you’re done.

how to Use the INDIRECT Function in Google Sheets 23

how to Use the INDIRECT Function in Google Sheets 24

 

Note that the Sheet Name is coming from cell A2, so the sheet’s name should be the same as the row names.

how to Use the INDIRECT Function in Google Sheets 25

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.

See also  How to Use Conditional Formatting in Google Sheets [Complete Guide]

Download/Copy Google Sheets Practice Workbook

Important Notes

  • 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

See also  How to Freeze a Row and Column in Google Sheets

Conclusion

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.

Content Protection by DMCA.com

M. Shaiq Ansari

Hi, I am Shaiq. A young and self-motivated content writer having years of experience expertise in MS Office suite, Google docs Editor Suite. I have a technical education background that empowers me to stand out in today's digital world. I am currently a freelance content creator and a part of a local digital marketing agency in Karachi Pakistan.

OfficeDemy.com
Logo
Enable registration in settings - general