The approach to creating the dynamic range is different than the approach used for OFFSET or INDIRECT.For those functions, the range is created within the function.
For example, adding a value to any blank cell will cause all volatile formulas (and any other formulas that depend on them) to recalculate.
Depending on your model design and size, this can have a major impact on the fluidity and responsiveness of the model.
And then in columns, C, D, and E we would array-enter over the entire range that extends as far down as the countries: To be clear, this means that if we had 99 countries in column B, we would select C2: E100, and then in the formula bar we would type the above formula, and then enter it by Control-Shift-Enter.
The end result is that the one formula is executed just once (in array fashion) and is brutally fast leaving a very light, non-volatile footprint in your model.
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.If we need to return multiple columns of metrics for a list of countries, the speed benefit is even greater.Suppose that GDP is the 2nd column of the data and that Capital was the 11th column.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.INDEX can be used to create a dynamic range, and not only is it nonvolatile, it is way faster than either OFFSET or INDIRECT.