Excel Function Friday: INDEX for Dynamic Range

Last Friday, there was an HLOOKUP example, and it used a dynamic lookup range — as rates were added to the lookup table, it automatically expanded to include them.
Today, we’ll take a closer look at that dynamic range, and see how the INDEX function is used to set the last cell in the range.

Count the Cells

To find the last column for the INDEX function, you can use the COUNT function to count the rates that are entered in row 2.
=COUNT(\$2:\$2)

Create a Cell Reference

The INDEX function can return a cell reference, based on a row number and column number in a reference.
INDEX(reference,row_num,column_num,area_num)
Using row 2 as the reference, the COUNT function can provide the column number. There is only 1 row in the reference, so 1 is used as the row number:
=INDEX(\$2:\$2,1,COUNT(\$2:\$2))

This formula creates a reference to cell D2, and its value is shown in cell B7.

Create the Dynamic Range

The same INDEX formula can be used in a defined name, to create a dynamic range. The range starts in cell A1 on the Rates sheet, and ends in the cell reference created by the INDEX function.
=Rates!\$A\$1:INDEX(Rates!\$2:\$2,1,COUNT(Rates!\$2:\$2))

If a rate is added or removed in row 2, the COUNT function result will change, and the INDEX function will return a different ending cell for the dynamic range.