It can often be incredibly time-consuming to search for a specific entry in an Excel table by hand, which is where VLOOKUP comes into play. This practical function allows you to find the exact value for a specific search criterion. The VLOOKUP function is indispensable for managing price lists, members directories, and inventory catalogues. To ensure you can benefit from this practical function,...
When using Excel, we often use cell references in formulas. This is what makes Excel so useful in the first place: Instead of performing calculations manually – like math back in school – by entering the values directly into the formulas, in Excel we simply specify the cells from which the values should be taken, across entire columns and rows. Microsoft’s spreadsheet program is therefore dynamic.
The INDIRECT function in Excel lets you go one step further. Rather than writing the cell reference in the formula, you can simply obtain it from another cell or compile it based on multiple parameters. It sounds complicated, but it can be a great help!
Why Use the INDIRECT Excel Function?
With INDIRECT, Excel allows you to obtain a reference from another cell, instead of entering it directly into the formula. Users can also piece together cell references in this manner – extracting the first component from one cell and the second from another. The cell containing the INDIRECT function then displays the value to which the compound reference refers.
INDIRECT is rarely used on its own. However, it could be used in this way for certain dynamic formulas, where the cell reference changes continuously with new parameters entered in a cell. In most cases, INDIRECT is used in combination with other functions.
It’s also possible to use INDIRECT to display a value from another worksheet. In theory, you could even refer to another workbook. But this kind of reference only works when the other workbook is also opened. If the file referred to in the INDIRECT function is closed, however, an error message will appear instead of a value.
INDIRECT comprises a required and an optional parameter.
- Reference: This is where you enter the cell reference or cell references. You can also write a part of the cell reference into the formula directly (using quotation marks). Two reference components are combined with the ampersand character (&).
- A1: This optional parameter specifies the format you wish to create the cell reference in. If the parameter is left out or TRUE is entered in the formula, the A1 format is applied. In the case of FALSE, the formula uses the R1C1 format.
Excel is typically used in A1 format. Here, the rows are numbered (A1, A2, A3, ...) and the columns shown with running letters (A1, B1, C1, ...). But you can also configure the use of the R1C1 format in the program settings; both rows and columns are numbered in this case. Accordingly, R5C10 would denote the cell where the fifth row meets the tenth column.
The R1C1 notation comes from Multiplan, Excel’s predecessor. You can still activate the reference style today in the program’s options under Formulas.
Examples of INDIRECT in Excel
For instance, if the cell A1 contains B1 and the cell B1 contains the value 5, the following formula would produce the value 5:
Excel recognizes the text entry in cell A1 as a signal for the cell reference and interprets it accordingly.
The function becomes more complex when the cell reference is split into parts.
=INDIRECT("B" & A1)
In this example, there is just one number in cell A1. The column component of the reference is entered into the formula directly as a letter.
But you can also write the row as well as column in two different cells.
=INDIRECT(A2 & A1)
INDIRECT also offers an interesting application when the formula is used to reference values in other worksheets. This can be useful, for example, if similar tables and calculations are contained in multiple sheets and you wish to display the selected values of other sheets in an overview sheet. Here, the name of the worksheet is obtained from a cell.
Cell A1 contains the name of the worksheet and in that sheet, the desired value is located in cell C1. The additional symbols in the function’s parameter arise from the fact that the full text of the reference (including quotation and exclamation marks) have to be included. For the necessary quotation mark, a single quotation mark needs to be placed within double quotation marks, since it concerns a text entry in the function. The text from cell A1 and subsequently a longer text entry then follows: The worksheet name is closed again with a quotation mark followed by an obligatory exclamation mark and finally the cell whose value you wish to display.
You can now either adjust the value in cell A1 – using a drop-down menu for example – as necessary or arrange the values of the different sheets one below the other. Here you can simply drag down the formula (using the cursor) and Excel will automatically adjust the references to the new position of the function.
Excel: INDIRECT and Other Functions
The INDIRECT Excel function is often used in conjunction with others. For example, combining INDIRECT with the SUM function is useful if you wish to add values from another worksheet. Rather than generating the total in the corresponding sheet and then reproducing it in an overview, like in the example earlier, the values can be added directly in the overview.
This formula allows you to add cells B1 to B5 in a worksheet that you have specified in cell A3.
At the same time, you can add values from different worksheets with the SUM function.
Many other mathematic or statistical functions can also be expanded in Excel using this technique (such as MEAN).