Ever need to make a formula where you have the ability to change the name of the range it is referring to on-the-fly via the value of another cell?
The formula INDIRECT() helps us achieve this.
For example I have the following formula:
VLOOKUP($A8, March, 3, false)
Now what if you didnt want to have to change the months manually every few weeks to generate a report? Yes you could do a find/replace. However a more elegant solution would be to simply write April on a cell and have the formula call and process the array with that name.
Below is what it would look like if I made it so that I entered the name of the month onto cell B4.
VLOOKUP($A8, INDIRECT($B$4), 3, false)
Great, now you dont have to seek out all the formulas where you wrote March and replace the name range in each formula. This formula will save you time if planned right.