Excel INDIRECT Function—The Best Guide with 5 Examples
This tutorial on the INDIRECT function in Excel is suitable for users of Excel 2013/2016/2019 and Microsoft 365.
This guide deals with the INDIRECT function in Excel.
We’ll cover,
Objective
Use the Excel INDIRECT function to indirectly reference cells and ranges.
The Excel INDIRECT Function — Explained
The INDIRECT function is part of the Lookup and Reference group of functions in Excel. It is unique in many ways because it is an Excel function that does not perform any calculations, evaluate conditions, or perform logical tests. So, what does INDIRECT do, and why is it useful?
Related:
Excel Sumifs & Sumif Functions – The No.1 Complete Guide
Excel Dynamic Array: 8 Must-know Formulas
Using The Let Function In Excel
Excel INDIRECT is used to indirectly reference cells, ranges, other sheets, or workbooks. It lets you return the reference to a cell, based on its string representation. As a result, you can change a cell reference within a formula without changing the formula itself.
INDIRECT Function Syntax
The INDIRECT function has only two arguments:
INDIRECT(ref_text, [a1]) - ref_text (required) – A reference to a cell that contains an A1-style reference, an R1C1-style reference, a name defined as a reference or a reference to a cell as a text string.
- a1 (optional) – A logical value that specifies what type of reference is contained in the cell ref_text.
The type of ref_text argument is specified by a1.
When a1 is TRUE or is omitted, then ref_text is considered as an A1-style cell reference.
When a1 is FALSE, then it is considered as an R1C1 style reference.
The A1 style is the normally used reference type in Excel. We use it all the time, whenever we reference a cell. It is done by specifying the column name followed by the row name of the cell. For Example, C22 refers to the cell in Column C at Row 22.
The R1C1 style is the inverse of the A1 style. In R1C1, the rows are followed by columns. For example, R4C2 refers to cell B4 which is in row 4, column 2 in a sheet. If there is no number after the letter, then it means we are referring to the same row or column.
Still, confused? The best way to understand the Excel INDIRECT function is to see some examples.
Video Tutorial for INDIRECT Function
How to use the INDIRECT function?
The Excel INDIRECT function has many uses. We’ll illustrate all of them with these examples.
Example 1 – A basic INDIRECT
Let us look at a basic example of INDIRECT. This formula will pull the value ‘600’ in cell G2 into cell A2. It does this indirectly by first referencing cell D2. D2 contains a cell reference to G2.
=INDIRECT(D2) // THE D2 CELL CONTAINS A REFERENCE TO CELL G2.
// G2 HOLDS THE VALUE OF 600.
// HENCE THE FUNCTION RETURNS A VALUE OF 600
Example 2 – INDIRECT with SUM
In this example, I have some data that shows sales figures by region for ten different sales agents. I aim to be able to type the region into cell G4 and have it return the total number of sales for that region in cell H4.
I can do this by combining the SUM function with the INDIRECT function.
To make this process quicker and easier, I have named each of the ranges for North, South, East, and West. If you are unsure how to name ranges, click here.
- Click in cell H4
- Type =SUM(INDIRECT(G4))
=SUM(INDIRECT(G4)) // INDIRECT(G4) RETURNS THE REFERENCE TO RANGE
//"NORTH", SINCE G4
// CONTAINS "NORTH" IN IT. HENCE THE SUM() FUNCTION
// FINDS THE SUM
// OF VALUES IN THE "NORTH" RANGEThis formula references the data in cell G4, in this case, ‘North’ and then sums the cell range named ‘North’.
- Change the region in cell G4 to ‘West’
The formula will update and produce the correct result.
Also Read:
The Best Excel Project Management Template In 2021
How To Use Excel Countifs: The Best Guide
How To Protect Cells In Excel Workbooks-the Easiest Way
Example 3 – INDIRECT with R1C1 Referencing
In this example, we are going to use the Excel INDIRECT function and R1C1 Referencing to get the last value in a table even if that table grows or expands.
NOTE: R1C1 Referencing is an alternative way of referencing cells in a spreadsheet. By default, you will more than likely use the A1 style of referencing when working in Excel e.g.; cells are referenced using their column letter and row number, B2, C10, D12, etc.
R1C1 Referencing stands for Row and Column. So instead of referencing columns by their letter, you would use a number. Column A = 1, column B = 2 etc.
The aim here is to get the last sales figures for July and pull them into cell J4. Bear in mind next month the Aug figures will be added to column I, so we want to make sure that the formula updates accordingly.
- Click in cell J4
- Type =INDIRECT(“R15C”&COUNTA(15:15),FALSE)
=INDIRECT(“R15C”&COUNTA(15:15),FALSE) // FALSE Specifies that you are
// using the R1C1 referenceThis formula uses R1C1 Referencing to find the total for the last column, in this case, July. The total is in row 15, and I have used the COUNTA function to find the number of columns currently in row 15. We use this method so that if the table expands and more data is added next month, the formula will still work correctly. The FALSE argument tells Excel we are using R1C1 Referencing, whereas a TRUE argument is A1 Referencing.
If we add in the figures for August in column I, the INDIRECT formula we have in cell J4 updates correctly.
Example 4 – Excel INDIRECT and Data Validation Lists
In this example, I am going to use the INDIRECT function to create two dynamic data validation drop-down lists. I am aiming to create a drop-down list in cell B14 that lists the countries and a drop-down list in cell B15 that lists the tours available in whichever country is selected in cell B14.
I have created named ranges for ‘Countries’, ‘Bali’, ‘India’, ‘Thailand’, and ‘Australia’.
- Click in cell B14
- Click the Data tab
- From the Data Tools group, click Data Validation
- Select List in the Allow drop-down
- In the Source, enter =Countries
- Click OK
A drop-down list will now appear in the Country field.
Now, we need to create a second list to display the Tours. This list will dynamically change depending on the Country selected.
- Click in cell B15
- Click the Data tab
- From the Data Tools group, click Data Validation
- Select List in the Allow drop-down
- In the Source, type =INDIRECT($B$14)
The INDIRECT formula references the country listed in cell B14 and matches it to the named range of the same name to produce the correct drop-down list.
Example 5: How to Lock a Cell Reference using INDIRECT function
The Excel INDIRECT function is very useful to lock cell references in formulas. This is very useful because when we add or delete rows or columns in Excel, the cell references inside formulas change automatically. This can cause a lot of confusion. To avoid this use the INDIRECT function.
In the picture below, the INDIRECT function for A2 fixes the reference to cell A2 and the plain reference for A3 is dynamic.
When a new column is inserted, the normal reference still returns 100, as its formula automatically changes to B3.
On the other hand, the cell with the INDIRECT reference now returns 0, because the formula does not change and still refers to the empty A1 cell.
FAQs
What is the INDIRECT function in Excel?
The Excel INDIRECT function returns a reference to the specified range. This means that you can use it to point to cells without directly referencing them. This function has interesting uses like locking cell references, creating variable worksheet names, creating variable drop-down lists etc.
What is the INDIRECT formula used for?
The Excel INDIRECT function is mainly used to change the reference to a cell in a formula without actually changing the formula.
Let’s Wrap Up
These are just a few important examples of how to use the Excel INDIRECT function. You will find more interesting ways to use the INDIRECT formula in Excel as you explore and gain experience.
Like what you see? For more high-quality guides on Excel explore our Excel courses section.
For more information on the Excel INDIRECT function, check out the following link:
Contextures – The Excel INDIRECT function
For more Free Excel tutorials from Simon Sez IT. Take a look at our Excel Resource Center.
Other Excel classes you might like:
- Logical Functions in Excel
- Designing Better Spreadsheets in Excel
- Introduction to Power Pivot & Power Query in Excel
To learn Excel with Simon Sez IT. Take a look at the Excel courses we have available.
Deborah Ashby
Deborah Ashby is a TAP Accredited IT Trainer, specializing in the design, delivery, and facilitation of Microsoft courses both online and in the classroom.She has over 11 years of IT Training Experience and 24 years in the IT Industry. To date, she's trained over 10,000 people in the UK and overseas at companies such as HMRC, the Metropolitan Police, Parliament, SKY, Microsoft, Kew Gardens, Norton Rose Fulbright LLP.She's a qualified MOS Master for 2010, 2013, and 2016 editions of Microsoft Office and is COLF and TAP Accredited and a member of The British Learning Institute.
