How to Use INDIRECT Function in Excel (12 Suitable Instances)

In Microsoft Excel, the INDIRECT function is generally applied to store a cell reference and then use the reference value with other functions to perform multiple operations in the spreadsheets. In this article, you’ll get to learn how you can apply and use this INDIRECT function efficiently in Excel.

indirect function in excel overview

The above screenshot is an overview of the article which represents an application of the INDIRECT function in Excel. You’ll learn more about the dataset as well as the methods to use the INDIRECT function properly in the following sections of this article.


Download Practice Workbook

You can download the Excel workbook that we’ve used to prepare this article.


Introduction to the INDIRECT Function

indirect function syntax in excel

  • Function Objective:

Storing data from the reference specified by a text string.

  • Syntax:

=INDIRECT(ref_text, [a1])

  • Arguments Explanation:
Argument Compulsory/Optional Explanation
ref_text Compulsory Reference of a cell with A1 or R1C1 format.
[a1] Optional Format of the cell to select- A1 or R1C1 
  • Return Parameter:

The function returns with the value(s) present in the stored reference.


12 Suitable Instances of Using INDIRECT Function in Excel

1. Using INDIRECT Function to Convert Text String into Cell Reference

By using the INDIRECT function, we can convert a text string into a cell reference very easily. In the INDIRECT function, we have to input the cell name with column name and row number inside the double quotes(” “). In the picture below, an example of converting a text string into a cell reference is shown.

As we’re going to find the value of Cell B5, so we have to type in Cell B8:

=INDIRECT("B5")

text to cell reference with indirect function in excel


2. Using Ampersand to Define Row and Column Index with INDIRECT Function

By using Ampersand(&) too, we can connect column name and row number to mention a cell reference. In the following picture, two lists of some random numbers are lying.

We’ll find out the value present in Cell C8. So, in the output Cell E7, the related formula will be:

=INDIRECT("C"&8)

defining cell reference by using ampersand with indirect function in excel


3. Using ROW Function inside the INDIRECT Function to Define Cell Reference

When you enter a ROW function in a cell, the function will return the row number of that corresponding cell. By using Ampersand(&) and ROW functions together, a cell reference can be mentioned in the INDIRECT function. In the picture below, 3 columns have some random numbers and in Cell F7, we’ll define only the column name C and by using the ROW function inside, the INDIRECT function will connect to the corresponding row number-7.

So, the related formula in Cell F7 will be:

=INDIRECT("C"&ROW())

indirect with row function in excel


4. Calculations with Cell References Defined by the INDIRECT Function

As the INDIRECT function stores a cell reference or a range of cells, we can use this function to perform different calculations by inserting other functions outside. Let’s assume, in the following picture, Column B represents a range of numbers and in the output Cell D9, we’ll sum the values present in the range of cells B5:B14. So, the related formula in the D9 Cell should be:

=SUM(INDIRECT("B5:B15"))

calculation with indirect function in excel


5. Locking Cell References with INDIRECT Function in Excel

When you input a cell reference or a range of cells inside the double quotes(” “) in the INDIRECT function, the cell references will then work as absolute cell references. For example, in the picture below, Column B has a number of multiplicands and Column C has a number or a multiplier- 5.

If you don’t use an absolute cell reference for C5 while multiplying the numbers in Column B with the multiplier, then the multiplier will not be the same for all multiplicands. So, using the INDIRECT function to lock a cell reference will sort out this problem.

In the output Cell D5, the related formula to lock a cell reference C5 will be:

=B5*INDIRECT("C5")

After pressing Enter and using Fill Handle to autofill the rest of the cells in Column D, we can find the products for all multiplicands.

locking cell reference with indirect function in excel


6. Using Cell Reference from Another Worksheet with INDIRECT Function

As the INDIRECT function locks a cell reference or a range of cells, we can use this function to copy a cell or a range of cells from another worksheet. For example, in the picture below, a random chart containing the names of donors and the donation amounts of day 1 is present in Sheet 1.

cell reference from another worksheet with indirect function in excel

Similarly, Sheet 2 represents another chart for day 2.

cell reference from another worksheet with indirect function in excel

Now, by using INDIRECT and MAX functions, we’ll find out the maximum amount of donations in Sheet 3 for the first two days. We have to define the cell references of other worksheets with their names while inserting them in the INDIRECT function to extract data from the related worksheets.

So, in the output Cell D5, our formula to find the maximum donation amount on day 1 will be:

=MAX(INDIRECT(C5&"!C5:C9))

After pressing Enter, you’ll get the return value for the first day. Now, by filling down the next output cell, you’ll get the maximum amount of donation from the second day. As you’re using the INDIRECT function here to lock a range of cells from other worksheets, so you don’t have to use the Dollar($) symbol to define absolute cell references.

cell reference from another worksheet with indirect function in excel


7. Using Cell Reference from Another Workbook with INDIRECT Function

We can also use and copy cell references from another workbook by using the INDIRECT function. The screenshot below represents a workbook named Donation Data that is open with Day 1 spreadsheet. We’ll extract the value of the Cell C7 to another workbook with an INDIRECT formula. Please keep in mind you must keep reference workbook open while extracting data from that workbook.

using cell reference from another workbook with indirect function in excel

📌 Step 1:

➤ Let’s open another new workbook first.

➤ In Cell B4, type ‘=’ and click on the Cell C7 of the Donation Data workbook.

using cell reference from another workbook with indirect function in excel

📌 Step 2:

➤ Press Enter and you’ll be shown the value of $1200.

➤ Keep this Cell B4 in text format, so remove the Equal(=) symbol from the cell by editing.

Our objective is to create this formula by using the INDIRECT function.

➤ Now in Cells C6 and C7, type the names of the Excel workbook and the spreadsheet respectively from where our data will be extracted.

using cell reference from another workbook with indirect function in excel

📌 Step 3:

➤ In the output Cell C9, type:

=INDIRECT("'["&C6&".xlsx]"&C7&"'!$C$7")

➤ Press Enter and you’ll be shown the extracted data at once from the defined workbook.

using cell reference from another workbook with indirect function in excel


8. Inserting INDIRECT Function to Refer an Array for VLOOKUP Function

Now we’ll see a suitable use of the INDIRECT function to extract data based on criteria from different charts or tables. For example, in the picture below, two charts of shop 1 and shop 2 are present with the names of some aquarium fishes but with different prices. In another chart next to these two, we’ll extract the prices of the fishes based on the defined shops.

refer to an array with indirect and vlookup in excel

At first, we have to define the names and the prices of the fish from two different shops. Here, I have defined the range of cells B6:C9 as Shop_1 and B13:C16 as Shop_2. If you need to know how to define a name for a range of cells you can go here to get some useful ideas on the procedures.

Now, in the output Cell G9, we’ll extract the price for Tetra fish from shop 2 by using VLOOKUP function. The VLOOKUP function looks for a leftmost column of a table and then returns a value in the same row from a column you specify. The syntax of this VLOOKUP functions is:

=VLOOKUP(lookup_value, table_array, col_index_number, [range_lookup])

So, the related formula incorporating VLOOKUP and INDIRECT functions in our output Cell G9 will be:

=VLOOKUP(E9,INDIRECT("Shop_"&F9),2,FALSE)

After pressing Enter and auto-filling the rest of the cells in Column G, you’ll get the prices of all fishes based on the specified shops.

refer to an array with indirect and vlookup in excel


9. Using INDIRECT Function to Refer Another Cell Reference from a Specific Cell

If you want to store a cell or a range of cells without using double-quotes(“ “) outside, then you have to define the corresponding cells with column names and row numbers in some other cells in your Excel spreadsheet at first. After that, you can use the INDIRECT function to store that cell containing the references of the defined cell or the range of cells.

For example, in the picture below, Column B has some random numbers and Column D represents the cell names of those corresponding numbers in Column B. In our output Cell F10, we’ll find out the number present in Cell B11. As the cell name B11 is defined in the Cell D11, so the related formula in the output cell will be:

=INDIRECT(D11)

After pressing Enter, you’ll find the return value as 21 that is lying in Cell B11.

referring to another cell reference from a specific cell with indirect function in excel


10. Referring to a Named Range with INDIRECT Function in Excel

Referring to a named range with the INDIRECT function in Excel is way too easy. Assuming that we have a table like in the picture below with the names of some people and their heights. We’ll define the name of this entire table first.

referring named range with indirect function in excel

📌 Step 1:

➤ Select the entire table.

➤ In the Name Box, edit the name of the table with Height_data or anything you prefer.

referring named range with indirect function in excel

📌 Step 2:

➤ In Cell E8, define the name of that chart or table.

➤ Now, select a random blank Cell G4 and type:

=INDIRECT(E8)

➤ Press Enter and you’ll find the entire table at once there.

This method is very useful when having to define an array for a wide range of calculations.

referring named range with indirect function in excel


11. Transposing a Table with INDIRECT and ADDRESS Functions in Excel

ADDRESS function creates a cell reference as text, given specified row and column numbers. The syntax of this function is:

=ADDRESS(row_num, column_num, [abs_num], [a1], [sheet_text])

In our dataset below, there are names, heights and ages of some random people. By combining INDIRECT as well as ADDRESS functions, we’ll transpose this entire table.

indirect with address function in excel

📌 Step 1:

➤ Select the output Cell B14 and type:

=INDIRECT(ADDRESS(COLUMN(B6) - COLUMN($B$4) + ROW($B$4), ROW(B6) - ROW($B$4) + COLUMN($B$4)))

➤ Press Enter and the formula will return as the text value ‘Name’.

indirect with address function in excel

📌 Step 2:

➤ Use Fill Handle to drag to the right until the last name in Column B is shown.

indirect with address function in excel

📌 Step 3:

➤ Use Fill Handle again to fill down the rows to show all the heights and ages of the people and you’re done.

indirect with address function in excel

After formatting the cells, the output results will look like in the picture below.

indirect with address function in excel

🔎 How Does This Formula Work?

➤ In the first argument of the ADDRESS function, “COLUMN(B4) – COLUMN($B$4) + ROW($B$4)” defines the row number by converting the column number of Cell B4.

➤ Similarly, in the second argument, “ROW(B4) – ROW($B$4) + COLUMN($B$4)” defines the column number by converting the row number of Cell B4.

➤ During the use of Fill Handle along the columns and rows later, all the other cells from table B4:D12 get transposed.


12. Using INDIRECT Function for Data Validation in Excel

For data validation to show drop-downs in Excel, the INDIRECT function paves the way. For example, there are 3 smartphone brands with a number of corresponding model names in the following picture. What we want to do is create the drop-downs of all smartphone brands with the corresponding model names.

📌 Step 1:

➤ Select the range of cells B5:B10 first which represents the model names of Apple products.

➤ In the Name Box, define the name of this range of cells with Apple.

data validation with indirect function in excel

📌 Step 2:

➤ Now in Cell B12, type Apple where the drop-down will be shown.

➤ Then select the Cell B12.

➤ From the Data ribbon, choose Data Validation command from the Data Tools drop-down. A dialogue box will appear.

data validation with indirect function in excel

📌 Step 3:

➤ Select the Data Validation criteria as List from the Allow drop-down.

➤ Under the Source bar, type:

=INDIRECT(B12)

➤ Press Enter and you’re done.

data validation with indirect function in excel

Now go to Cell B12 and you’ll find the drop-down for all Apple products there. By following similar procedures, you can create drop-downs for the other two smartphone brands.

data validation with indirect function in excel


💡 Things to Keep in Mind

🔺 If you don’t use double-quotes(“ “) while referring to a cell or a range of cells as a text string, the function will return #REF error if the specified cell does not contain any other cell references.

🔺 Unless you define the format of the cell reference in the 2nd argument, the default format will be in A1 style.

🔺 When you’ll use the INDIRECT function to copy or extract a cell reference from another workbook, that corresponding workbook must be kept open otherwise, you’ll be shown #REF error.


Concluding Words

I hope all of the methods mentioned above to use the INDIRECT function will now prompt you to apply them in your Excel spreadsheets more effectively. If you have any questions or feedback, please let me know in the comment section. Or you can check out our other articles related to Excel functions on this website.


Further Readings

Nehad Ulfat

Hello, Welcome to my profile. I'm a Technical Content Creator as well as a Naval Architect & Marine Engineer. I have preferences to do analytical reasoning & writing articles on various statistical data. Here, you'll find my published articles on Microsoft Excel & other topics related to my interests!

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo