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.

How to Use Excel INDIRECT Range: 8 Easiest Ways

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.

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.

⏩ In cell I4, type the following formula.

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.

Read More: How to Use the INDIRECT Function to Get Values from Different Sheet in Excel

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.

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.

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.

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.

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.

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.

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.

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

Download to Practice 

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

• How to Use Indirect Address Functions in Excel
• INDIRECT Function with Sheet Name in Excel
• Create Drop-Down List Using INDIRECT Function in Excel
• How to Convert Text to Formula Using the INDIRECT Function in Excel

<< Go Back to Excel INDIRECT Function | Excel Functions | Learn Excel