INDIRECT Function in Excel

INDIRECT Function In Excel

The INDIRECT excel function is used to indirectly refer to cells, cell ranges, worksheets, and workbooks. The function accepts a text string entered as an argument and converts it into a valid cell address. Next, it goes to this cell address and returns its value as is.

For example, in the succeeding image, the cell B1 contains the text string “c4.” Enter the formula “=INDIRECT(B1)” in any cell, say D7. This INDIRECT excel formula works as follows:

  1. The INDIRECT function refers to cell B1, which consists of “c4.”
  2. The INDIRECT function goes to cell C4 and returns its value “Jessica” in cell D7.

In this way, the text string “c4” is converted into the cell address C4. The INDIRECT excel function heads to this cell address (C4) and returns its value (Jessica).

Indirect Function in Excel (Jessica)

The INDIRECT function lets the user specify an indirect cell address which is in the form of a text string. Apart from cells, this function can also refer to named ranges, other workbooks, and worksheets with the help of text strings.

The advantage of using the INDIRECT excel function is that the indirect references do not change with the insertion or deletion of new rows or columns. Moreover, the user can change the text string (indirect cell address) of the cell referred to in the formula, without making any changes to the INDIRECT formula itself.

For instance, in the preceding example, type “c2” (without the double quotation marks) in cell B1 and retain the formula “=INDIRECT(B1).” So, this time the INDIRECT function goes to cell C2 and returns its content. Hence, the text string changed rather than the INDIRECT excel formula.

The purpose of using the INDIRECT excel function is to convert text values into valid cell addresses. The INDIRECT function is categorized under the Lookup and Reference functions of Excel.

Syntax of the INDIRECT Function

The syntax of the INDIRECT function of Excel is shown in the following image:

indirect function formula

The function accepts the following arguments:

a1When true or omitted or 1, the “ref_text” argument is considered to be in A1-style.
a1When false or 0, the “ref_text” argument is considered to be in R1C1-style.

The argument “ref_text” is mandatory while “a1” is optional.

Note 1: If the text string is entered in R1C1-style reference (like r2c3), it is compulsory to enter the “a1” argument (as 0 or false) in the INDIRECT excel formula.

Note 2: For more on A1-style and R1C1-style references, refer to the topic “how is the A1-style different from the R1C1-style of referencing?” at the end of example #5.

How to Use the INDIRECT Function in Excel?

Let us consider a few examples to understand the working of the INDIRECT function in Excel.

You can download this INDIRECT Function Excel Template here – INDIRECT Function Excel Template

Example #1–Reference to a Named Range

The following table displays the number of visitors to the website www.xyz.com. These visitors have been categorized under different channels. Each channel is listed in the column “search channel.”

Further, these visitors belong to five different countries, namely India, USA, Australia, Singapore, and Canada. Each country is represented by its respective column.

We want to perform the following tasks:

INDIRECT Function in Excel Example 1
The steps to perform the given tasks are listed as follows:

Step 1: In row 8, calculate the total visitors of each country by applying the SUM function. The totals of columns B, C, D, E, and F are shown in the succeeding image.

Create a named range for the data of each country. Let us begin by naming the range F3:F7 as “Canada.” For this, perform the following tasks:

a. Select the range F3:F7.

b. In the name box, type “Canada” without the double quotation marks. The name box is to the left of the formula bar and is shown with a red arrow in the succeeding image.

c. Press the “Enter” key.

The range F3:F7 has been named “Canada.”

Note: The names of the named ranges are not case-sensitive. This implies that if a range is named “figures,” the terms “FigUres,” “fIguReS,” etc. mean the same to Excel.

INDIRECT Function in Excel Example 1-1

In the following pointers (step 1a to step 1c), the alternative method to create a named range has been discussed.

Step 1a: Select the range (F3:F7) to be named. Click “name manager” from the Formulas tab of the Excel ribbon. Click “new.”

INDIRECT Function in Excel Example 1-2

Step 1b: The “new name” box opens. Next, perform the following tasks:

a. In the “name” box, type “Canada” without the double quotation marks.

b. In “refers to,” the selected range F3:F7 is displayed. If this range is not showing in the “refers to” box, select it.

c. Click “Ok.” Close the “name manager” dialog box.

The selected range (F3:F7) has been named “Canada.” The selection of this range has been shown in the following image as well as the image under step 1a.

INDIRECT Function in Excel Example 1-3

Step 1c: Likewise, create named ranges for the numbers of all the five countries. The five named ranges titled Australia (D3:D7), Canada (F3:F7), India (B3:B7), Singapore (E3:E7), and USA (C3:C7) have been created.

The named ranges are shown in the following image.

INDIRECT Function in Excel Example 1-4

Step 2: To calculate the total number of visitors from Canada, enter the following formula in cell I4.

“=SUM(INDIRECT(H4))”

The INDIRECT excel function refers to cell H4, which contains the name “Canada.” It goes to the range “Canada” (F3:F7) and returns its total. This is because the INDIRECT function is enclosed within the SUM function.

INDIRECT Function in Excel Example 1-5

Step 3: Prior to applying the data validation feature, a named range consisting of the names of the five countries needs to be created. For this, perform the following tasks in the given sequence:

a. Select “name manager” from the Formulas tab and click “new.” The “new name” dialog box opens.

b. In the “name” box, type “country” (without the double quotation marks), as shown in the following image.

c. In the “refers to” box, select the cells B2:F2. Click “Ok.”

d. Close the “name manager” window.

A named range titled “country” is created.

INDIRECT Function in Excel Example 1-6

Step 4: Apply the data validationData ValidationData validation in Excel is used to limit a user's inputs to specified cells or input ranges by offering pre-defined inputs for selection to avoid multiple redundant data inputs.read more feature to cell H4. For this, perform the following tasks in the stated order:

a. Select the cell (H4) to which the data validation feature has to be applied.

b. Click the “data validation” drop-down from the Data tab of Excel. Select “data validation.”

c. The “data validation” window opens, as shown in the following image. In the “settings” tab, select “list” from the “allow” drop-down.

d. In “source,” type “=country” without the double quotation marks.

e. Select the checkboxes of “ignore blank” and “in-cell dropdown.” Click “Ok.”

The data validation feature has been applied to cell H4. Depending on whether the checkbox of “in-cell dropdown” is selected or not (in step d), a drop-down arrow may or may not appear next to cell H4.

In this case, the drop-down arrow does appear next to cell H4, though it has not been shown in the subsequent images. This drop-down arrow (next to cell H4) shows the names of the five countries, India, USA, Australia, Singapore, and Canada.

Note: The data validation feature restricts the kind of input that a user can enter in a cell of a worksheet. It allows the user to enter only those values that have been defined by the data validation rule.

INDIRECT Function in Excel Example 1-7

Step 5: Once the data validation feature has been applied, enter the name of any of the five countries (India, USA, Australia, Singapore, and Canada) in cell H4. The total number of visitors of the selected country will appear in cell I4.

To enter the name of a country in cell H4, one can either type the required country or select it from the drop-down arrow next to the cell H4.

If an input other than the names of the given five countries is entered in cell H4, an error message appears. This message states that restrictions have been defined for this cell (H4).

For instance, enter “India” in cell H4, the sum of its visitors (30,019) appears in cell I4. This is shown in the following image.

INDIRECT Function in Excel Example 1-8

In the following pointers (step 5a to step 5b), the results of the data validation feature are examined.

Step 5a: Enter “USA” in cell H4. The sum 29,242 appears in cell I4, as shown in the following image.

INDIRECT Function in Excel Example 1-9

Step 5b: Enter “Australia” in cell H4. The total number of visitors (30,336) appears in cell I4. The same is shown in the following image.

Hence, one must note that we did not change the INDIRECT excel formula of cell I4 to obtain the total visitors of the different countries. We only changed the names of the ranges in cell H4.

Thus, we changed the content of the cell referred in the formula (H4), without changing the formula itself (in cell I4).

INDIRECT Function in Excel Example 1-10

Example #2–Reference to a Worksheet

The succeeding images (image 1, image 2, and image 3) show the sales revenue (in $) generated by the different channels (under “search channel”) in different countries. Every channel represents a category of visitors to the website www.xyz.com.

Further, one image has been created in one Excel worksheet. As a result, every worksheet has been named by the country it represents. For instance, the worksheet containing the numbers of India has been named “India.”

The total sales revenue of five countries (India, USA, Australia, Singapore, and Canada) has been shared. These totals have been calculated with the SUM function of Excel.

We want to perform the following tasks:

  • Calculate the total sales revenue generated by each country with a combination of the INDIRECT and the SUM functions.
  • Cross-check the results of the INDIRECT function in excel (enclosed within SUM) with the simple SUM function.

The total sales revenue of Singapore and Canada are $65,358 and $20,600 respectively. For these countries, assume that the sales generated by the different channels are listed in the range C2:C8 of the worksheets titled “Singapore” and “Canada” respectively. Cell C2 contains the caption “sales” and cell C8 contains the sum of the range C3:C7 (calculated by the SUM function).

[Please note that the total sales revenues calculated with the SUM function have been shared to enable cross-checking of results.]

Image 1

The total sales revenue (in $) of India is shown in the following image:

[Consider the entire column “search channel” as range B2:B8 of the worksheet “India.” The column “sales” is in the range C2:C8. These ranges also include the titles of the green boxes.]

INDIRECT Function in Excel Example 1-11(India)

Image 2

The total sales revenue (in $) of USA is shown in the following image:

[Consider the column “search channel” as range B2:B8 of the worksheet “USA.” The column “sales” is in the range C2:C8.]

INDIRECT Function in Excel Example 1-12 (USA)

Image 3

The total sales revenue (in $) of Australia is shown in the following image:

[Consider this image as a part of the worksheet titled “Australia.”]

INDIRECT Function in Excel Example 1-13 (Australia)

The steps to calculate the total sales revenue of the different countries by using the INDIRECT excel function are listed as follows:

Step 1: To find the total sales revenue generated by each country, concatenate (join) the name of the worksheet and the channel-wise sales figures (listed in the “sales” column of the preceding images). This is because the sales figures of different countries are in different worksheets.

In cell C4, begin typing the following INDIRECT excel formula.

“=SUM(INDIRECT(B4”

The same is shown in the succeeding image.

INDIRECT Function in Excel Example 1-16

Step 2: Complete the preceding formula (entered in step 1) in cell C4.

“=SUM(INDIRECT(B4&”!C3:C7))”

The same is shown in the following image.

The “ref_text” argument (B4&”!C3:C7) begins with cell B4, which contains “India.” “India” is the name of the worksheet which contains image 1. So, in the first argument of the INDIRECT function, we refer to the worksheet indirectly by its name.

Further, we use the operator ampersand (&) for combining the sheet name (India) in cell B4 and the range to be summed up (C3:C7) in image 1.

INDIRECT Function in Excel Example 1-17

Step 3: Press the “Enter” key. The output appears in cell C4, as shown in the following image.

Since the INDIRECT function in excel is enclosed within the SUM function (refer to the formula entered in step 2), the sum of the range C3:C7 of worksheet “India” is returned.

Hence, the total revenue $199,363 matches the “sum” amount of image 1. This implies that the result of the preceding formula (in step 2) is the same as that of the simple SUM function (in the second succeeding image).

INDIRECT Function in Excel Example 1-18

To allow cross-checking of results, the application of the SUM function to the range C3:C7 has been shown in the following image.

INDIRECT Function in Excel Example 1-19

In the following pointers (step 3a to step 3c), the total sales revenue of the different countries are examined.

Step 3a: For calculating the total sales revenue of USA, perform the following tasks:

  1. Enter “USA” (without the double quotation marks) in cell B4.
  2. Enter the formula “=SUM(INDIRECT(B4&”!C3:C7))” in cell C4.
  3. Press the “Enter” key.

In the given INDIRECT excel formula, the value of cell B4 (worksheet named “USA”) is joined with the range (C3:C7 of image 2) to be summed.

Hence, the total revenue $1,030,984 appears in cell C4. This number is the same as the “sum” amount of image 2.

INDIRECT Function in Excel Example 1-20

Step 3b: Enter “Singapore” in cell B4. The formula to be entered in cell C4 remains the same as that of the preceding step (step 3a).

Press the “Enter” key. The output $65,358 appears in cell C4, as shown in the following image.

INDIRECT Function in Excel Example 1-21

Step 3c: Enter “Canada” in cell B4. Retain the same formula as that of the preceding step (step 3a).

The output in cell C4 is $20,600.

INDIRECT Function in Excel Example 1-22

Likewise, the total sales revenue of Australia can be calculated by entering the respective name in cell B4 and retaining the formula of cell C4.

Hence, by changing the sheet names (in cell B4) and retaining the formula (in cell C4), we have obtained the total sales revenue of the different countries. Please note that no changes were made to the INDIRECT formula (in cell C4).

Thus, we changed the values of the first argument “ref_text” (different sheet names), though the cell address of the INDIRECT excel formula remains the same (B4).

Example #3–Reference to the Last Month (R1C1-Style)

The succeeding images (image 1, image 2, and image 3) show the sales revenue (in $) generated by the different channels (under “search channel”) in the first three months of 2018. The entire data relates to the website www.xyz.com.

We want to perform the following tasks:

  • Find out the total sales revenue of the last month (March) by using the R1C1 reference style of the INDIRECT function in excel.
  • Add a fifth column (column E) to the existing dataset, which contains the sales of April. Find out the total sales revenue of the last added month (April) by using the R1C1-style of the INDIRECT function.
  • Cross-check the output of the INDIRECT formulas with the totals of the respective months. These totals have been calculated with the help of the SUM function of Excel.

The figures of April are–$12,943 (organic search), $7,279 (direct visitor), $7,889 (paid search), $10,327 (social visitors), $2,154 (referral), and $40,592 (total).

Image 1

The channel-wise sales revenues (in $) for January, February, and March are shown in the following image:

INDIRECT Function in Excel Example 1-23

Image 2

The total sales revenue ($) for January, February, and March are shown in the following image:

INDIRECT Function in Excel Example 1-24

Image 3

The total sales revenues of March and April are to be calculated in the cell B13 (containing a question mark) shown in the following image:

INDIRECT Function in Excel Example 1-25

The steps to perform the given tasks are listed as follows:

Step 1: Begin by entering the following formula in cell B13.

“=INDIRECT(“R10C”&COUNTA(10:10))”

The same is shown in the following image.

Step 2: Complete the preceding formula (of step 1) in cell B13.

“=INDIRECT(“R10C”&COUNTA(10:10),FALSE)”

The same is shown in the following image.

The first argument of the INDIRECT excel function is “R10C”&COUNTA(10:10). In this argument, we have concatenated R10C and the COUNTA functionCOUNTA FunctionThe COUNTA function is an inbuilt statistical excel function that counts non-blank cells (not empty) in a cell range or the cell reference. For example, cells A1 and A3 contain values but cell A2 is empty. Therefore, the formula “=COUNTA(A1, A2, A3)” returns 2.read more with the help of the operator “&.” The COUNTA function counts the total number of non-empty cells in row 10. It returns 4.

So, R10C joined with 4 results in the cell number R10C4. Since the second argument of the INDIRECT function is false, R10C4 is recognized as an R1C1-style reference by Excel.

Note: For more on R1C1-style references, refer to the topic “how is the A1-style different from the R1C1-style of referencing?” at the end of example #5.

Step 3: Press the “Enter” key. Since the “ref_text” argument evaluates to R10C4, the INDIRECT excel formula returns the value of the cell, which is at the intersection of row 10 and column 4. In the A1-style, this is cell D10 of Excel.

Hence, the output is $249,344.

INDIRECT Function in Excel Example 1-28

Step 4: Add the sales figures of April in column E of the existing dataset. Enter the following formula in cell B13.

“=INDIRECT(“R10C”&COUNTA(10:10),FALSE)”

The first argument of the INDIRECT excel function is “R10C”&COUNTA(10:10) and the second argument is false. The COUNTA function returns the value 5 as there are five non-empty cells in row 10.

When R10C is joined with 5, it results in R10C5. This R10C5 is the R1C1-style reference which implies cell E10 (A1-style). This is because the row number is 10 and the column number is 5 (column E).

Hence, the INDIRECT excel formula returns the value of cell E10. So, the total sales revenue for April is $40,592.

INDIRECT Function in Excel Example 1-29

Likewise, we can add the sales data of the remaining months to the existing dataset. With the same INDIRECT formula (entered in steps 2 and 4), we can obtain the total sales revenues of the last added month.

One can notice that even after adding the data of April, the INDIRECT excel formula remained the same. In other words, the INDIRECT formula we used for calculating the sales revenue of March is the same as that of April.

The INDIRECT formula did not change because it perceives “R10C” in the “ref_text” argument as a text string.

Hence, the indirect references do not change with the insertion or deletion of new rows or columns in Excel.

Example #4–Reference to a Table Array

The following image contains five table arrays. Each array contains the sales revenue generated from the different visitor channels (under “search channel”) of a website (www.xyz.com).

One table array represents the data of one country. For the five countries, India, USA, Canada, Australia, and Singapore, the table arrays are named “Ind,” “States,” “Can,” “Aus,” and “Sin” respectively.

In addition, the entire range of every table array is a named range. The name of the range is the same as the name of the table array. For instance, the range D2:E7 has been named “Ind,” G2:H7 has been named “States,” and so on.

We want to perform the following tasks:

  • Obtain the sales of the “direct visitor” channel of Singapore (in cell B3) with the help of the VLOOKUP function of Excel.
  • Obtain the sales of the “direct visitor” channel of Singapore (in cell B3) by using a combination of the VLOOKUP and INDIRECT functions.
  • Obtain the sales of the “referral” channel of India (in cell B3) by using a combination of the VLOOKUP and INDIRECT excel functions.

One can cross-check the output of the preceding pointers with the figures of the respective table arrays.

Example 1-30

The steps to perform the given tasks are listed as follows:

Step 1: To obtain the sales of the “direct visitor” channel of Singapore by the VLOOKUP function, perform the following tasks:

a. Enter “direct visitor” (without the double quotation marks) in cell B1.

b. Ensure that the range G10:H15 is named “Sin.” Thereafter, enter “Sin” in cell B2.

c. Enter the formula “=VLOOKUP(B1,B2,2,0)” in cell B3.

d. Press the “Enter” key.

The output “N/A” error appears in cell B3, as shown in the following image.

The “N/A” error appears because we have mistakenly entered the “table_array” argument (second argument) as “B2.” The VLOOKUP function could not recognize this argument (B2). This is because the “table_array” argument is always entered as a range of cells.

Hence, an incorrect argument makes the VLOOKUP formula return an error.

Had we entered the VLOOKUP formula as “=VLOOKUP(B1,G10:H15,2,0)” in cell B3, the output would have been $4,587.

Example 1-31

Step 2: To obtain the sales of the “direct visitor” channel of Singapore by a combination of the VLOOKUP and INDIRECT functions, perform the following tasks:

  1. Enter “direct visitor” in cell B1 and “Sin” in cell B2. The range G10:H15 has already been named “Sin” in the preceding step (step 1).
  2. Enter the formula “=VLOOKUP(B1,INDIRECT(B2),2,0)” in cell B3, as shown in the following image.

The first argument, cell B1 is the “lookup_value” of the VLOOKUP function. So, this function searches for the string “direct visitor.”

The INDIRECT excel function refers to cell B2, which contains “Sin.” This “Sin” is the “table_array” argument of the VLOOKUP function. The VLOOKUP function searches for the string “direct visitor” in the range “Sin” (G10:H15).

Further, the “col_index_num” argument is 2. So, the VLOOKUP function searches for the lookup value (direct visitor) in the second column of the range “Sin.”

Since the fourth argument (range_lookup) is 0, the given VLOOKUP formula returns an exact match. An exact match is a value which is exactly equal to the lookup value.

Example 1-32

Step 3: Press the “Enter” key. The output $4,587 appears in cell B3. This output matches with the value of cell H12 which is a part of the table array of Singapore.

Hence, the preceding formula (entered in step 2) has returned the sales of the “direct visitor” channel of Singapore.

Example 1-33

Step 4: To obtain the sales of the “referral” channel of India with the help of the VLOOKUP and INDIRECT functions, perform the following tasks in the stated order:

  1. Enter “referral” in cell B1 and “Ind” in cell B2.
  2. Ensure that the range D2:E7 is named “Ind.”
  3. Enter the formula “=VLOOKUP(B1,INDIRECT(B2),2,0)” in cell B3.
  4. Press the “Enter” key.

The output appears in cell B3, as shown in the succeeding image.

This time we have changed the values of cells B1 and B2 to “referral” and “Ind” respectively. Since named ranges are not case-sensitive, “IND” and “Ind” mean the same to Excel.

The first argument (lookup_value) of the VLOOKUP is “B1.” The output of “INDIRECT(B2)” works as the “table_array” argument of the VLOOKUP function. The “col_index_num” argument is 2. The “range_lookup” argument is 0 or false (exact match).

The VLOOKUP searches for the value “referral” in the named range “Ind” (D2:E7). It returns an exact match from the second column of the range “Ind.”

Hence, the sales of the “referral” channel of India are $3,192. This output does match with the value of cell E7. This cell is a part of the table array of the country India.

Example 1-34

Likewise, we can change the values of cells B1 and B2 to obtain the different sales numbers in cell B3.

It must be noted that the formulas entered in steps 2 and 4 are the same. Retain this formula to obtain the required sales figures in cell B3. The values of cells B1 and B2 must be changed according to the output required in cell B3.

Example #5–Reference to a Drop-down List

The following image shows three designations (director, manager, and supervisor) prevalent in an organization. These positions (designations or ranks) are listed in column A. Columns C, D, and E state the names of those employees who are currently serving at these positions.

We want to perform the following tasks:

  • Create a drop-down list in cell I2. When the drop-down arrow is clicked, the list must display the names of the three positions (director, manager, and supervisor).
  • Create a dependent drop-down list in cell I3. As a position is typed in cell I2, the corresponding names of employees (serving at that position) should be displayed. For instance, if “director” is typed in cell I2, cell I3 should show the names of the directors (listed in column C) of the organization.

Use the named ranges, data validation feature, and the INDIRECT function of Excel.

Example 1-35

The steps to perform the given tasks are listed as follows:

Step 1: Name the ranges of the given dataset. For naming the ranges, select the range and type the required name in the name box (refer to step 1 of example #1).

The ranges should be named as follows:

  • Name the range A2:A4 as “position.”
  • Name the range C2:C3 as “director.”
  • Name the range D2:D5 as “manager.”
  • Name the range E2:E7 as “supervisor.”

Note: While naming the ranges, do not enter the beginning and the ending double quotation marks (shown in the preceding pointers) in the name box.

Step 2: For creating a drop-down list in cell I2, perform the following steps:

  1. Select cell I2. Click the “data validation” drop-down from the Data tab of Excel. Select “data validation.”
  2. In the settings tab of the “data validation” window, select “list” under the “allow” drop-down.
  3. Under “source,” type “=position” (without the double quotation marks).
  4. Select the checkboxes of “ignore blank” and “in-cell dropdown.”

A drop-down list is created in cell I2, as shown in the following image. When the drop-down arrow (next to cell I2) is clicked, the names of the three positions (director, manager, and supervisor) appear.

To enter a position in cell I2, one can either type it or select it from the drop-down list.

Example 1-36

Step 3: To create a dependent drop-down list in cell I3, we need to apply the data validation feature to the given cell. For this, perform the following steps:

a. Select cell I3. Click the “data validation” drop-down from the Data tab of the Excel ribbon.

b. Select “data validation.” The “data validation” window opens.

c. In the “settings” tab, select “list” under “allow.”

d. Select the checkboxes of “ignore blank” and “in-cell dropdown,” in case they are not selected.

e. Under “source,” enter the formula “=INDIRECT($I$2)” without the double quotation marks. Click “Ok.”

The same is shown in the following image. Once “Ok” is clicked (in step e), the drop-down arrow appears in cell I3.

Example 1-37

In the following pointers (step 3a to step 3c), the different drop-down lists (in cell I3) that appear on typing the position (in cell I2) have been examined.

Step 3a: Type “director” (without the double quotation marks) in cell I2. Clicking the drop-down arrow of cell I3 displays the names of employees, Andrew and Micheal. These employees are serving at the mentioned position.

The same is shown in the following image.

Example 1-38

Step 3b: Type “manager” (without the double quotation marks) in cell I2. The drop-down of cell I3 displays the names Camille, David, Davis, and William. These employees are the managers of the organization.

Example 1-39

Step 3c: Type “supervisor” (without the double quotation marks) in cell I2. The drop-down of cell I3 displays the names of the employees working as supervisors in the organization. They are Alex, Diana, Sam, Austin, Shawn, and John.

Example 1-40

It must be noted that the drop-down list of cell I3 is dependent on cell I2. This implies that cell I3 displays the names of employees corresponding to the name of the position typed in cell I2.

Prior to creating a dependent drop-down list (in cell I3), ensure that all ranges (in columns A, C, D, and E) are named and thereafter, apply the data validation feature to cell I3.

How is the A1-Style Different From the R1C1-Style of Referencing?

In the A1-style of referencing, every cell address consists of a column letter followed by a row number. For instance, the address C5 refers to the cell at the intersection of column C and row 5.

On the other hand, in the R1C1-style, a cell address consists of a row number followed by a column number. Instead of letters, every column has a unique number assigned to it. For instance, cell A2 in A1-style is referred to as cell R2C1 in R1C1-style.

To change the Excel reference style from A1 to R1C1, perform the following steps:

  1. From the File tab of Excel, click “options.”
  2. The “Excel options” window opens. Select “formulas.”
  3. Under “working with formulas,” select the checkbox of “R1C1 reference style.”
  4. Click “Ok.”

Consequently, all the column letters will change to numbers. Moreover, if there are any formulas in the worksheet, their cell references will adjust according to the R1C1-style.

By default, the INDIRECT function of Excel uses the A1-style of referencing. However, the referencing style can be changed to R1C1 by entering 0 or false in the “a1” argument.

The Cautions While Working With the INDIRECT Excel Function

The cautions to be observed, while working with the INDIRECT function of Excel, are listed as follows:

  • The “ref_text” argument must be a valid cell reference. If not, the INDIRECT function will return the “#REF!” error.
  • The “ref_text” argument can refer to another workbook (an external reference). Ensure that the concerned workbook (source workbook) is open. If not, the INDIRECT function will return the “#REF!” error.
  • The “ref_text” argument must not refer to a cell range beyond the maximum of 1,048,576 rows or 16,384 columns (XFD). If it does, the INDIRECT function in excel returns a “#REF!” error.

The “#REF!” error is shown in the following image.

Note: In the modern versions of Excel, XFD is the right-most column of a worksheet.

Example 1-41

Frequently Asked Questions

1. Define the INDIRECT function of Excel.

The INDIRECT function returns the value of a cell whose address is provided by a text string. In other words, the function is capable of interpreting text strings as valid cell addresses (or cell references). Once interpreted, the function goes to this cell address, picks its value, and returns it as is.

The INDIRECT function does not perform any calculations. It just evaluates a reference and displays its content. The text string (or the reference) entered in the function can be changed without actually changing the INDIRECT formula.

The syntax of the INDIRECT function of Excel is stated as follows:

“INDIRECT(ref_text,[a1])”

The “ref_text” argument is the cell address which contains a text string, a named range or the name of a worksheet or workbook.

The “a1” argument is the value true or false. If “a1” is true or 1 (or omitted), the “ref_text” argument is treated as an A1-style reference. If “a1” is false or 0, the “ref_text” argument is considered to be in R1C1-style.

2. How should the INDIRECT function be used with the data validation feature of Excel?

With the help of the INDIRECT function and the data validation feature, it is possible to create dependent drop-down lists. A dependent drop-down list displays a series of items depending on the selection of a value in another cell.

For example, there are two lists titled “flowers” and “trees” in an Excel worksheet. Let us create a dependent drop-down list in cell E3. This list should be dependent on the value entered in cell E2.

The text “flowers” is displayed in cell A1. This list contains the following items:

• Cell A2 contains Rose
• Cell A3 contains Lotus
• Cell A4 contains Sunflower
• Cell A5 contains Tulip

The text “trees” is shown in cell B1. This list contains the following items:

• Cell B2 contains Oak
• Cell B3 contains Pine
• Cell B4 contains Maple
• Cell B5 contains Spruce

The steps to create a drop-down list in cell E3, which depends on cell E2, are listed as follows:

a. Name the range A2:A5 as “flowers” and B2:B5 as “trees.”
b. Select cell E3.
c. Click the “data validation” drop-down from the Data tab of Excel. Select “data validation.”
d. In the “settings” tab, select “list” under the “allow” drop-down.
e. Under the “source” drop-down, enter the formula “=INDIRECT($E$2).” This is because the list (of cell E3) should depend on the value selected in cell E2.
f. Select the two checkboxes of “ignore blank” and “in-cell dropdown.” Click “Ok.”

A dependent drop-down list is created in cell E3.

When “flowers” is typed in cell E2, the drop-down arrow next to E3 displays the list–Rose, Lotus, Sunflower, and Tulip. Similarly, if “trees” is typed in cell E2, cell E3 shows the values–Oak, Pine, Maple, and Spruce.

Note: Please do not enter the double quotation marks in the name of the range (in step a) and the INDIRECT excel formula (in step d).

3. Why and when should the INDIRECT function be used in Excel?


The INDIRECT function of Excel should be used for the following reasons:

a. It helps refer to a cell, range, worksheet or workbook indirectly.
b. It allows fixing a reference so that it does not change with the insertion or deletion of rows or columns.
c. It helps obtain different outputs by changing the text string to which the INDIRECT formula refers.

The INDIRECT function of Excel should be used in the following situations:

a. It is used when there is a need to create dynamic cell references. For creating a dynamic reference to a range, enter its name in a cell, and refer to that cell in the INDIRECT formula [like “=INDIRECT(C1)” where the cell C1 contains the name of the range].
b. It is used when there is a need to convert a text string into a valid cell address. The text string supplied to the function can be changed as per the user requirement, without making any changes to the actual INDIRECT formula.
c. It is used when there is a need to lock a cell reference. A cell reference is locked to prevent it from changing with the insertion or deletion of rows or columns. For instance, the formula “=INDIRECT(“B2”)” identifies “B2” as a text string rather than a cell reference. The string “B2” remains intact with the insertion of a row preceding cell B2 (like row 1).

Recommended Articles

This has been a guide to the INDIRECT Excel function. Here we discuss how to use INDIRECT formula in Excel along with step by step examples. Take a look at the following articles of Excel–

  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>

Reader Interactions

Leave a Reply

Your email address will not be published. Required fields are marked *