In this blog post we look at 5 examples of the INDIRECT function of Excel. This is a very misunderstood function, which can be incredibly useful and powerful.
Prefer to watch the video? The video tutorial below will demonstrate all 5 INDIRECT function examples.
What is the INDIRECT Function?
Before I delve into these different INDIRECT examples, I should quickly what it is for those of you who may be new to the function.
INDIRECT is one of the lookup functions of Excel. And it’s purpose is to return a reference which is specified by a piece of text (typically some text in the cell of a worksheet).
This becomes really useful when you concatenate that information together with others to form a reference to a different sheet, particular cell or named range. You will see these in the tutorials.
The INDIRECT function looks like below.
=INDIRECT(ref_text, [A1])
The ref_text is a reference to a cell that contains, or partially contains, an address.
The A1 argument is optional. It determines the type of reference for INDIRECT to use. If you type True then the normal A1 style is used. If you type False then the R1C1 style is used.
Ok, let’s get stuck into these tutorials.
1. Using INDIRECT with Named Ranges
A great use for INDIRECT is with named ranges. The concept of naming ranges for simpler referencing has huge benefits, and when you understand INDIRECT those benefits are amplified.
In the example below, cell G4 contains the total for the sales of the region mentioned in F4. In this image, it is for Cardiff.
This total was achieved using the formula below.
=SUM(INDIRECT(F4))
Each range of values has been named after the region. For example range D3:D10 is named Cardiff, and range B3:B10 is named Southampton.
The INDIRECT function returns the value from F4. This value is then converted from text to a reference so that the SUM function can add the values from the named range.
Without INDIRECT the SUM function would not use the value in F4 as a reference to the named range.
2. INDIRECT to Dynamically Reference other Worksheets
In this example, we want to reference a different worksheet within a formula, which is another SUM function. The worksheet we are summing values from is dependent upon the value in a cell. So once again INDIRECT is needed.
The image below shows the summary sheet, and cell B3 contains the name of the sheet that we will be summing from. The beauty of this technique is that if someone changes the value in cell B3, it then sums from that sheet.
The formula below has been used in this example. In each sheet the values to be summed are in range C4:C11.
=SUM(INDIRECT("'"&B3&"'!C4:C11"))
The apostrophes have been used in the formula to enclose the sheet name. If a sheet name contains spaces such as South Africa, apostrophes are required.
3. Return the Last Value from a Row
In this example, we will look at using an R1C1 style reference with INDIRECT to return the last value from a row.
In the image below the formula in cell F3 is returning the last value in row 10. This ensures that as new months (columns) are added to the table, it always retrieves the current months value.
The formula below has been used in cell F3. By using an R1C1 reference style instead of the traditional A1 style, we can refer to the column numerically. The COUNTA function has been used here to count the non blank cells of row 10, therefore returning the last column.
=INDIRECT("R10C"&COUNTA(10:10),FALSE)
This is concatenated onto the R10C part to complete the reference as row 10 and column 4.
The FALSE part of the INDIRECT formula requests the R1C1 style reference.
4. INDIRECT and VLOOKUP
It feels necessary to include an example of using INDIRECT with the VLOOKUP function. For this example INDIRECT will be used to create a conditional lookup table.
In the image below a VLOOKUP function has been entered into cell B3 to return the sales value for Jessica (B1) and Cardiff (B2).
Each table has been named appropriately as Southampton, Liverpool and Cardiff. Just like in the first example of this blog post, INDIRECT has been used to convert the value of cell B2, to a reference to a named range.
The formula below is the VLOOKUP used. The table array is specified by changing the value in cell B2.
=VLOOKUP(B1,INDIRECT(B2),2,FALSE)
5. Create Dependent Drop Down Lists
Another brilliant use of INDIRECT is to create dependent drop down lists. This is a great technique if you work with large lists.
You can break the large list up into multiple smaller lists and then make them dependent on one another. So the selection from one list would determine what options appear in the next list.
This demonstration also uses named ranges as a clever way of referencing different lists. Check out the video below to see how it is done.
Check out this video on how to create multiple dependent drop down lists.
If any of these INDIRECT examples are not clear, check out the video at the top of this blog post which will show you exactly how each formula is done.
Leave a Reply