How to create a dynamic named range with INDEX

UnlikeINDIRECTandOFFSET, INDEX is a non-volatile function.

This means that INDEX will not recalculate whenever a change is made to a worksheet.

This makes INDEX ideal for professional models and for worksheets that contain a large amount of data.

However, INDEX is a complex function that takes time to understand.

Remember that we need to useabsolute referencessince we’re creating adynamic named range.

First, let’s figure out the last row in this data.

For that, we can use theCOUNTA functionwith all of column A as the reference.

The result is “11” since column A contains 11 non-empty cells.

This returns “8”.

Now let’s use INDEX to get a reference to the last cell in column A.

The result is “Status” but, as before, INDEX is actually returning a reference to H1.

Now let’s use INDEX to get a reference to the last cell in the data.

For that, we need to give INDEX a range that’s bigger than our data will ever be.

In this case, let’s use the first one-thousand rows of the worksheet.

Next, we’ll use the COUNTA formulas we already figured out to supply both the column and row.

The result is the string “Under contract.”

Finally, we’re ready to build the INDEX formula we’ll use to compute a dynamic range.

This is the clever part.

We know that the formula above is returning H11, the last cell in the range.

The result is a formula that computes the full data range.

Finally, let’s check that the range is dynamic.