Excel’s most useful function is the INDIRECT function, which lets you dynamically reference cells based on text strings. This can be pretty helpful for automating repetitive operations, developing dynamic dashboards, and developing flexible formulas. In this post, we’ll explore the versatility of the INDIRECT Function. uncover how it can revolutionize how you approach Excel data manipulation and analysis. So, buckle up as we dive into the world of the INDIRECT Function and discover the hidden capabilities it brings to your Excel toolkit. Let’s begin by exploring the syntax and arguments of the INDIRECT function. Following that, we’ll delve into usage notes and examples to seamlessly integrate this powerful function into your Excel toolkit.
Syntax & Argument
The syntax for the INDIRECT Function is
=INDIRECT(ref_text,[a1])
While the INDIRECT function has two arguments, only one is required: a reference text string. This text string can take various forms, including named range and cell references, if it evaluates to a valid cell reference.
- ref_text: This is a required argument representing the text string specifying the cell’s reference. It could be a direct cell reference (e.g. “A1”) or a text string that evaluates to a valid reference.
- [a1]: This is an optional argument to specify the reference style. If omitted, it assumes the default style in Excel, which is the A1-style referencing. True or omitted indicates an A1 style reference. For example, if “ref_text” is “A2” , the function will interpret it as the cell in column A and row 2. A FALSE value for [a1] indicates R1C1 style referencing. If this style is selected, the function interprets ‘ref text’ as a relative position from the current cell. For instance, “R[1]C[1]” refers to the cell one row down and one column to the right of the current cell.
We are all caught up with the arguments for the INDIRECT Function, let’s see some usage notes to help you better understand the function.
Usage Notes
- The strength of the INDIRECT Function lies in its ability to dynamically create cell references based on text inputs. This is very valuable for scenarios in which the references need to change dynamically, such as with dropdown menus.
- Using named ranges makes the INDIRECT function more dynamic because if the text string provided refers to a named range, the INDIRECT function will adapt to changes in the named range.
- The INDIRECT function can create references to cells or ranges in external workbooks. This can be achieved by constructing the text string with the workbook name, sheet name, and cell reference.
- An error-handling function, such as IFERROR, is needed to handle situations where the reference is not valid.
- The INDIRECT Function recalculates every time there is a change in the worksheet. This makes it a very volatile function that should be used with some caution, as overuse could impact performance in large or complex spreadsheets.
- To make sure your formulas function correctly when you copy or move them to different cells, use the A1 reference type option.
- To assess the formula and view the actual cell reference to which the INDIRECT function resolves, press the F2 key and then the F9 key while on the ref_text argument.
That’s it for the Usage notes of the INDIRECT function. Now, let’s take a look at some examples.
INDIRECT Function Examples
Indirect Function with Cell References
The following are some instances of using cell reference with the INDIRECT Function
=INDIRECT(“B”&2) // This returns the value of cell B2
Indirect Function with Named Ranges
The INDIRECT function can be used with named ranges to make your formulas more readable and maintainable. Let’s see some examples below:
=SUM(INDIRECT(“Expenses”)) // This sums the values in the range named “Expenses”
INDIRECT & VLOOKUP Combination
The INDIRECT function can be synergistically combined with other Excel functions to create robust and dynamic formulas. An excellent companion function is VLOOKUP. Let’s witness this synergy in action.
This uses the VLOOKUP function to search for a value in a range specified by the text string in cell A1. Utilizing the INDIRECT function with a named range as the table array for the VLOOKUP function proves highly beneficial, especially when anticipating future changes in the table array.
INDIRECT & SUMIF Combination
Another function that pairs well with the INDIRECT function is SUMIF. Let’s explore an example in action
In our example above, we utilized the INDIRECT Function to specify the range. This is very useful if we anticipate future changes in the table array.
Practical Example of INDIRECT Function
This section will explore using the INDIRECT function to create a dynamic dropdown list. In our example, we’ve organized a company’s directorates in one column. To the right, we duplicate the list of directorates horizontally. For each directorate, we include various account names relevant to it.
Using this data, we can create a dynamic dropdown list of Account Codes that automatically updates based on the directorate selected at the first selection point (column B).
To create the dynamic dropdown list, select cell C3 and navigate to Data > Data Validation.
In the pop-out box, click on the dropdown under Allow and select List.
Finally, type in the formula below and press ok.
=INDIRECT(B3)
Now let’s go to cell C3 and see what we have in our dropdown list
To copy this validation to Excel, select cell C3 and copy like you normally would. Then Press ALT + E + S. A selection box pops out, now, choose Validation and watch the magic happen.
