INDEX can be used to create a dynamic range, and not only is it nonvolatile, it is way faster than either OFFSET or INDIRECT.
In fact, the improvement in performance is so great that INDEX should be the foundation of all dynamic ranges in professional models.
With INDEX on the other hand, the dynamic range is produced by using INDEX on one side (or sometimes both) of the Range Operator, which is the colon.
For example, consider this normal looking range reference: By itself, this reference could be a hard reference to the country names in our list of countries.
Building on this idea, we can alter the named formula, d, so that it results in a dynamic range instead of a fixed range. A Dynamic range is often constructed with OFFSET or INDIRECT.
Unfortunately, both of these Microsoft Excel functions are volatile, which simply means that all formulas that include these functions will recalculate every single time anything on the worksheet changes.
If on a new worksheet, we wanted to output three columns of data for each country, Population, GDP, and Capital, the most efficient way to do so is to dedicate one column to create a common index, and then array-enter the INDEX formula over the entire three columns of output.
In this scenario, column B would be the list of countries. In A2, we would enter: ..then copy this formula down as far as the countries are listed in column B.
Suppose we have a table or list of metrics for countries in the range of A1: M200.
We can create a named formula that refers to this range - let's call it simply, d, for data.
From my perspective, the Excel INDEX function is the single most important in the roster of Microsoft Excel functions. Excel INDEX can return one value or an array of values; it can return a reference to one cell or to a range of cells.
Now that might be surprising considering the function's humdrum name, but please pay close attention, because INDEX is one of the magical secrets of how to use Excel! INDEX works well on either side of the three Reference Operators - the colon, the space, and the comma. Note: If you use an international version of Excel, some of the array constants may not work as presented.
The approach to creating the dynamic range is different than the approach used for OFFSET or INDIRECT.