Excel is one of the most comprehensive pieces of spreadsheet software available and comes with a wide range of functions to do different jobs. One of these is the INDIRECT function – but what does it do and how do you use it?
What is the INDIRECT function?
When you use Excel’s INDIRECT function, you can indirectly reference cells, ranges or other sheets. By using it, you can create a dynamic cell reference or range reference without the need to ‘hard code’ the reference into the formulas you use. This also means that you can change the cell reference in the formula while the actual formula remains the same.
The INDIRECT function is a built-in element of Excel that returns a reference specified by a text string. That text string can be anything from one cell to a range of cells or even a worksheet name. Learning to use it involves learning the basic syntax involved and how it works in action.
The INDIRECT function will look something like this:
=INDIRECT(ref_text, [a1])
In this, the ref_text is the first argument and is required. It should reference a cell that contains an A1 style reference, an R1C1 style reference, a name defined as a reference or a reference to a cell in a text string. So there needs to be a valid cell reference here, or the function won’t work. However, this cell can be in another workbook, providing that workbook is open.
The second argument, A1 is optional. It uses logical true or false values, and the purpose is to specify what type of cell the first argument refers to. Without this, Excel will default to a ‘true’ setting for the function and will assume the ref_text is an A1 style reference. ‘False’ will tell it that it is an R1C1 style reference.
Examples of the INDIRECT function
As with all Excel functions, it is often best to understand what they do by seeing them in action. One example is a dynamic lookup table made using Excel INDIRECT functions. The basic formula would be:
=VLOOKUP(A1,INDIRECT(“text”),column)
Here the purpose is to switch table ranges inside the lookup formula. Without the INDIRECT being included in the lookup formula, it won’t work because the INDIRECT resolves the text to a valid reference.
You can also use the function to get cell content at a given row and column. This would look like this:
=INDIRECT(ADDRESS(row,col))
In this, if you have a list of items such as a list of fruit for sale in the store, you can use the formula to find the location of a specific item. It will then place this information in the location specified in the (row,col) section of the formula.
How to carry out the INDIRECT function
A straightforward example of how to use the INDIRECT function is if you have a number in cell A1 and the text “A1” in cell C1. If you use the formula =INDIRECT(C1) in cell D1, you will see the number from cell A1 as this is the value of cell C1. The function knows to go for C1 to A1 to get the number required and places this in cell D1.
When you are creating your formula, you can opt for the A1 style or the R1C1 style. Most users go for the former because it is more straightforward, but there is a checkbox under File > Options > Formulas where you can select to use the latter if that’s what you prefer.
In addition to creating those simple references from cell values, you can also use it to combine a text string and a cell reference by tying them together with the concatenate operator ‘&’. For example, with the formula:
=INDIRECT(“B”&C2)
You would get a value from cell B2 that is based on the following logic chain – INDIRECT concatenates the elements of text B and value in C2 then the value of cell C2 which is a number then refers to cell B2. Therefore the formula will return the value in B2.
There are lots of other ways to use the formula including with words as well as numbers with named ranges being one example. You can also use it to reference another worksheet by using the name of that sheet in the formula to tell Excel where to use or place the information.
Make your spreadsheets work harder for you and speak to an Excel Consultant today.
