In this article, you will learn everything about the INDIRECT function in Excel. You will see the uses of the INDIRECT function and how you can use it for cell address, range, or sheet names.
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. You can use this function for indirect address to refer to the address of the cell rather than to the cell itself. For the indirect range, you will get the value of that selected range by simply mentioning it. You can also mention sheet names to get values from different sheets using this function. After finishing this article you will have a good idea about the INDIRECT function in Excel.
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. Here, you will also get some articles to use this function for some practical purposes.
Introduction to the INDIRECT Function
- 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.
How to Use INDIRECT Function in Excel: 12 Suitable Instances
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")
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)
3. Using the ROW Function inside the INDIRECT Function to Define Cell Reference
When you enter the 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())
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"))
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.
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.
Similarly, Sheet 2 represents another chart for day 2.
Now, by using the 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.
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 the 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 the reference workbook open while extracting data from that workbook.
📌 Step 1:
➤ Let’s open another new workbook first.
➤ In Cell B4, type ‘=’ and click on the Cell C7 of the Donation Data workbook.
📌 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.
📌 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.
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 fish based on the defined shops.
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 the 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 function 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.
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 Cell D11, 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.
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.
📌 Step 1:
➤ Select the entire table.
➤ In the Name Box, edit the name of the table with Height_data or anything you prefer.
📌 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.
This method is very useful when having to define an array for a wide range of calculations.
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.
📌 Step 1:
➤ Select the output Cell B14 and type:
=INDIRECT(ADDRESS(COLUMN(B4) - COLUMN($B$4) + ROW($B$4), ROW(B4) - ROW($B$4) + COLUMN($B$4)))➤ Press Enter and the formula will return as the text value ‘Name’.
📌 Step 2:
➤ Use the Fill Handle to drag to the right until the last name in Column B is shown.
📌 Step 3:
➤ Use the Fill Handle again to fill down the rows to show all the heights and ages of the people and you’re done.
After formatting the cells, the output results will look like in the picture below.
➤ 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 the 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.
📌 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 the Data Validation command from the Data Tools drop-down. A dialogue box will appear.
📌 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.
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.
How to Fix When INDIRECT Function Is Not Working in Excel
1. #REF! Error
While using this function, Excel may return #REF! Error in 3 cases.
- Invalid Cell Reference as ref_text: check the arguments of the function to make sure you have not inserted any cell reference that is invalid.
- Exceeding Range Limit: Excel 2007, 2010, and 2013 have a row limit of 1,048,576 and a column limit of 16,384. If you exceed this limit, #REF! Error may occur.
- Using Closed Worksheets or Workbooks: if you have closed the worksheet or workbook that you are using in the function, Excel will return this error. Make sure to open the worksheets or workbooks before using them in the function.
2. #NAME? Error
This error occurs for misspelled functions. Check the spelling of your function to solve this error.
💡 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 a #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 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 a #REF error.
Download Practice Workbook
You can download the Excel workbook that we’ve used to prepare this article.
Conclusion
I hope all the things mentioned above about the INDIRECT function will now prompt you to apply them in your Excel spreadsheets more effectively. You will also find some articles describing how to use this function for address referencing, with sheet names and to create a drop-down list. 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.
