HOW TO USE INDIRECT FUNCTION IN EXCEL ?

Table of Contents

INTRODUCTION

The functions are a very powerful tool present in the spreadsheet applications like EXCEL to solve many challenging problems.

The functions are a self contained entity which receives the variables and return the output as per the type of the function.

Every function is apt to be used in a particular situation.

A situation can arise when we need to use an address which is already present in any cell or as a textual statement.

For such a situation we have got a function called INDIRECT in EXCEL .

In this article we’ll learn everything from the introduction, purpose, examples and the usage of Indirect function in EXCEL .



PURPOSE OF INDIRECT FUNCTION IN EXCEL

INDIRECT FUNCTION RETURNS THE CELL REFERENCE GIVEN BY ANY STRING PASSED INTO THIS FUNCTION.

It simply means that we can enter any cell address as a TEXT as an argument and the function will point towards the address passed as a text.

This simple function is very useful and helps us to perform difficult procedures in EXCEL .

Some of the situations where we can make use of INDIRECT FUNCTION can be

  1. When the cell address is present as the text.
  2. To pass any address as a text.
  3. To use the dropdown and choose the address which will depend on the selected text.
  4. Choose any values which are present in the different cells which can be chosen by using indirect function.
  5. Can be used to use the R1C1 notation in Excel.

and many other conditions.

PREREQUISITES TO LEARN INDIRECT FUNCTION

THERE ARE A FEW PREREQUISITES WHICH WILL ENABLE YOU TO UNDERSTAND THIS FUNCTION IN A BETTER WAY.

  •  Basic understanding of how to use a formula or function.
  •  Basic understanding of rows and columns in Excel.
  •  Of course, Excel application.


SYNTAX: INDIRECT FUNCTION IN EXCEL

The syntax ( the way how formula is phrased for EXCEL ) of INDIRECT FUNCTION is

=INDIRECT ( REFERENCE TO A CELL AS STRING, IF THE NOTATION IS A1)

REFERENCE TO A CELL AS STRING is simply the address of the cell as a string / text which you want to refer.

IF THE NOTATION IS A1 If the reference is in the A1 style , choose TRUE if it is R1C1 style, choose FALSE.

If the second argument is skipped, it’ll be taken as A1 notation by default.

EXAMPLE 1: USE INDIRECT FUNCTION TO GET THE DATA IN THE CELL A1

DATA SAMPLE

For trying out our newly learn function INDIRECT , let us put some random data “WELCOME TO GYANKOSH.NET” in the cell A1.

In the solution, we’ll try to get this data using the INDIRECT FUNCTION by passing the cell address as text and getting the reference from the other cell too.

The data is shown in the following picture.


EXAMPLE DATA

Let us get the data in the cell B6 using the indirect function.0

If we want to get the data directly in the B6, we’ll use the formula as =A1 and it’ll bring the data to B6 directly. But here, we want to use the function indirect.

So follow the steps to use the indirect function to get the data.

STEPS TO GET THE DATA IN A1 USING INDIRECT FUNCTION

  •  Simply double click the cell where you want the result to get started.
  • Enter the formula as =INDIRECT(“CELL ADDRESS”, TRUE [IF A1 NOTATION ])
  • For our example, we’ll enter the formula in the cell B6 as =INDIRECT(“A1”,TRUE)

EXAMPLE 1: USING INDIRECT FUNCTION TO GET THE DATA FROM OTHER CELL
  • Press Enter.
  • The result will appear in the cell. The result will be the text WELCOME TO GYANKOSH.NET which is actually present in the cell A1.

In the cell B9, we tried the same formula without using the second argument. As we know that, the value is taken as TRUE by default which is verified as the result is same.

The formula used in the cell B9 is =INDIRECT(“A1”)



EXAMPLE 3: GET THE DATA IN THE CELL A1 USING THE R1C1 NOTATION

DATA SAMPLE:

We already referenced the cell A1 in various ways using the INDIRECT FUNCTION.

In this example we’ll refer to the cell using the R1C1 NOTATION.

Cell address A1 is R1C1 as the first row is 1 and first column A is 1.

STEPS TO GET THE DATA IN A1 USING R1C1 STYLE NOTATION

  •  Simply double click the cell where you want the result to get started.
  • Enter the formula as =INDIRECT(CELL ADDRESS CONTAINING THE ACTUAL LOCATION IN R1C1 NOTATION AS TEXT1, FALSE)
  • For our example, we’ll enter the formula in the cell B27 as =INDIRECT(“R1C1”,FALSE)
  • Press Enter.
  • The result will appear as WELCOME TO GYANKOSH.NET
EXAMPLE 3: GETTING THE DATA USING INDIRECT FUNCTION USING R1C1 STYLE OF ADDRESSING