How to Use the INDIRECT Function: A Comprehensive Guide
If you’re looking for a comprehensive guide on how to use the INDIRECT function in Excel, you’ve come to the right place! In this blog post, we’ll discuss what the INDIRECT function is and how you can use it to create valid references in your formulas. We’ll also provide some examples of how INDIRECT can be used to solve common problems that you may encounter in your spreadsheets. So without further ado, let’s get started!
What Does The INDIRECT Function Do?
The INDIRECT function returns a valid cell reference from a given text string. INDIRECT is useful when you want to assemble a text value and use it as a valid reference. The purpose of INDIRECT may at first seem baffling but there are many situations where the ability to create a reference from text is useful, including :
-A formula that needs a variable sheet name
-A formula that can convert text into a cell reference
-A fixed reference that will not change even when you delete rows or columns
Where To Find It?
Option 1:
You can find the INDIRECT function in the Formulas tab of Excel under Lookup & Reference
Option 2:
You can use Formula Builder to walk you through the formula step by step.
Option 3:
You can manually type the formula into any cell.
The Formula
=INDIRECT (ref_text, [a1])
- ref_text (required) – A reference supplied as text.
- a1 (optional) – A boolean to indicate A1 or R1C1-style reference. Default is TRUE = A1 style.
Let’s Take a Look at A Real-World Examples
Excel Workbook
Coming Soon
Example
Coming Soon
Your turn: Use the workbook and INDIRECT to follow the examples in blue and finish the practice formulas highlighted in green.
Tips and Tricks
Better Together
You can get the most out of the INDIRECT Function by using it with other functions. You can use it with any formula that expects a reference. Common uses include VLOOKUP, HLOOKUP, SUM, and SUMIF.
Volatility
INDIRECT is a volatile function that can cause performance issues in large excel workbooks. A volatile function is one that recalculates every time there is a change in the workbook.
#REF Error
The ref_text argument must generate a valid cell reference. If it doesn’t, you will get a #REF Error.
Let’s Recap
The INDIRECT function is a handy tool that can be used to create valid references in your Excel formulas. It’s useful for things like assembling text values into cell references, creating variable sheet names, and fixing references that won’t change even when rows or columns are deleted.
Have any questions? Are there other topics you would like us to cover? Leave a comment below and let us know! Make sure to subscribe to our Newsletter to receive exclusive financial news right to your inbox.
