Here are two different ways to use the INDIRECT function in Excel:
(Note: Video version above, text version below)
The INDIRECT function “returns a reference specified by a text string”. The syntax is =INDIRECT(ref_text,[a1]). ref_text is the text sting that you build to create the reference. a1, which is optional, indicates if you want the structure to be in the A1 format or the R1C1 format. Most use A1, and that is the default, so it is almost always left out of the structure.
Here we have a data range. I’ve used data validation to create drop down lists for the Month and Sales. I’ve also named the ranges for each month and salesperson using the Create from Selection option. You can see a post on both these features using the links below:
In the “Answer” cell we will put the following formula:
=INDIRECT(F16) INDIRECT(F17). The space between the two functions means “the intersection of”. So by referencing the Month and Sales fields of the drop down lists, which are named ranges, Excel will give us the value at the intersection of the two:
Another use of INDIRECT is to create a dynamic range of cells to sum based on the reference to two control cells. Here we have a list of numbers. I want to add a range of numbers within this list, say from A5:A12. I can use the formula =SUM(A5:A12), but if I want to change it, I have to manually go into the formula and edit it, or highlight the range that I want to change it to:
Instead, we can use the INDIRECT function within the SUM function to dynamically create a range to add. The formula we will insert will be =SUM(INDIRECT(“A”&B2&”:A”C2)). The result of the INDIRECT function will be A5:A12, the 5 coming from the reference of cell B2 and the 12 coming from the reference to cell C2:
Now, by just changing the numbers in cells B2 & C2, you can dynamically adjust the formula to add different ranges.