How to Use Excel INDIRECT Range (8 Easiest Ways)

The INDIRECT function (a Lookup & Reference function) is used to return the reference specified by the text string. This function is used to get values from any reference of a cell or a range. In this article, I’m going to explain the use of the Excel INDIRECT range.

To make the explanation clearer, I’m going to use a dataset that represents sales information for 4 quarters. The dataset has 5 columns these are Sales Person, Quarter-1, Quarter-2, Quarter-3, and Quarter-4.

Dataset of Excel INDIRECT Range


Download to Practice 


8 Ways to Use Excel INDIRECT Range

1. Using INDIRECT Range to Get Value Through Cell Reference

You can use the INDIRECT function to fetch value through the cell reference of a cell or cell range.

Here, I want to get the values of the cell range C4:C8 from Quarter-1.

To do it,

⏩ In cell H4, type the following formula.

=INDIRECT("C4:C8")

Using Excel INDIRECT Function Range to Get Value Through Cell Reference

Here, in the INDIRECT function, I used the cell range C4:C8 as a string/text (“C4:C8”) in ref_text.

Now, press ENTER, and you will get all the values of the used cell references.


2. Get Value Through Cell Reference in Cell 

In case you want to fetch values while keeping the cell or cell range in a cell then you also can do it by using the INDIRECT function.

Here, I want to get the values of the cell range D4:D8 from Quarter-2. Instead of using the cell reference directly, I want to keep it in a cell. Here, in the dataset given below, I place the cell range from where I want to fetch the value.

Get Value Through Cell Reference in Cell Using INDIRECT Range

⏩ In cell I4, type the following formula.

=INDIRECT(H4)

Here, in the INDIRECT function, I selected cell H4 as ref_text. Where I stored the cell range D4:D8 in the H4 cell. So, it will fetch the values of those cells.

Now, press ENTER, and you will get all the values from the selected cell.

Get Value Through Cell Reference in Cell Using INDIRECT Range


3. Using Value in a Cell Creating Reference

The INDIRECT function gives us the opportunity to create a cell reference using a value in a cell.

Let me show you the process of creating a reference.

⏩ In cell I4, type the following formula.

=INDIRECT("E"&H4)

Using Value in a Cell Creating Reference Using Excel INDIRECT

Here, in the INDIRECT function, I used “E”&H4 as ref_text. Where E is the column and in H4 I stored the value 5. In the function, I concatenated column E and the value of the H4 cell so that it will become E5. Finally, the INDIRECT function will fetch the value of the E5 cell.

Now, press ENTER, and you will get the values from the cell you used as a reference.


4. Find Out the SUM of a Range of Cells Using Named Range

You can use the named range in the INDIRECT function. Then by using other formulas you can calculate the values of the named range.

Let me show you the process of naming any range,

First, select the cell range.

➤ I selected the cell range C4:C8.

Then, go to the Address Bar and type any name of your choice to name the range.

➤ I named the selected cell Quarter_1.

Using Value in a Cell Creating Reference

Now, to use the named range to calculate total sales of Quarter_1, I’m going to use the INDIRECT and the SUM function together.

⏩ In cell I4, type the following formula.

=SUM(INDIRECT(I3))

Using Excel INDIRECT and SUM function together

Here, in the SUM function, I used INDIRECT(I3) as a number1.

In the INDIRECT function, I selected cell I3 as ref_text. Where in I3 I kept the name of the range C4:C8.

Now, the INDIRECT function will get the values of those cells and the SUM function will return the total of those cells.

Finally, press ENTER, and you will get total sales of Quarter_1.


5. Using INDIRECT Named Range to Find The AVERAGE 

You also can calculate the AVERAGE of a named range of cells using the AVERAGE function and the INDIRECT function.

⏩ In cell I4, type the following formula.

=AVERAGE(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The AVERAGE of Range of Cells

Here, in the AVERAGE function, I used INDIRECT(I3) as a number1.

In the INDIRECT function, I selected cell I3 as ref_text. Where in I3 I kept the name of the range C4:C8.

Now, the INDIRECT function will get the values of those cells and the SUM function will return the average of those cells.

Finally, press ENTER, and you will get average sales of Quarter_1.


6. Find Out The MAXIMUM of a Range of Cells

In case you want to find out the maximum of a named range for that you can use the MAX function and the INDIRECT function.

⏩ In cell I4, type the following formula.

=MAX(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The MAXIMUM of a Range of Cells

Here, in the MAX function, I used INDIRECT(I3) as a number1.

In the INDIRECT function, I selected cell I3 as ref_text. Where in I3 I kept the name of the range C4:C8.

Now, the INDIRECT function will get the values of those cells and the MAX function will return the maximum number from those cells.

Finally, press ENTER, and you will get the maximum sales of Quarter_1.


7. Find Out The MINIMUM of Range of Cells

If you want, you also can find out the minimum of a named range by using the MIN function and the INDIRECT function.

⏩ In cell I4, type the following formula.

=MIN(INDIRECT(I3))

Using Excel INDIRECT Range to Find Out The MINIMUM of Range of Cells

Here, in the MIN function, I used INDIRECT(I3) as a number1.

In the INDIRECT function, I selected cell I3 as ref_text, wherein I3 I kept the name of the range C4:C8.

Now, the INDIRECT function will get the values of those cells and the MIN function will return the minimum number from those cells.

Press ENTER and you will get the minimum sales of Quarter_1.


8. Using Excel INDIRECT Range to Find The AVERAGE of SMALL Values

You also can find out the average of the 3 smallest numbers from a range of cells by using the AVERAGE function, SMALL function, ROW function, and the INDIRECT function.

⏩ In cell I4, type the following formula.

=AVERAGE(SMALL(F4:F8,ROW(INDIRECT("1:3"))))

Using Excel INDIRECT Range to Find Out The AVERAGE & SMALL Values

Here, in the AVERAGE function, I used SMALL(F4:F8,ROW(INDIRECT(“1:3”))) as a number1.

In the SMALL function, I used F4:F8 as array and ROW(INDIRECT(“1:3”)) as k which is position.

Then, in the ROW function, I used INDIRECT(“1:3”) as a reference.

In the INDIRECT function, I used 1:3 as ref_text. Where 1:3 means 3 values. Here, the INDIRECT function is used in the ROW function so that the formula remains correct even if you insert or delete any rows.

Now, the SMALL function will return the 3 smallest numbers and the AVERAGE function will return the average of those smallest numbers.

Press ENTER and you will get the average of 3 smallest sales of Quarter_4.


Things to Remember

🔺 The INDIRECT function shows the #REF error if ref_text is not a valid cell reference and the range limit is exceeded.

🔺 The INDIRECT function will show the #NAME error if you misspell the function name.


Practice Section

Excel INDIRECT Range Practice Section


Conclusion

In this article, I have explained 8 ways to use Excel INDIRECT range. I also tried to cover the when and why the INDIRECT function may show errors frequently. Last but not least, if you have any kind of suggestions, ideas, or feedback please feel free to comment down below.


Related Articles

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo