# 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.

To see the defined name and the INDEX examples, you can download the INDEX dynamic range sample file.

______________

### 22 Responses

1. David Tyler says:

How does INDEX compare with OFFSET for dynamically named ranges? I’ve used something like =OFFSET(A2,0,0,1,COUNT(2:2)) to set the range for a problem like this, but I’m always looking for better solutions (otherwise I wouldn’t subscribe to 20 Excel RSS feeds).

Is INDEX a better solution, or just a different one? Or is there a time for INDEX and a time for OFFSET when creating dynamic ranges?

2. Tony says:

Aha !!!! ExcelFF. Kind of Excel for Tweet Freaks. Love it.

3. Lynda says:

@ David – I believe it’s a matter of calculation speed. OFFSET is volatile & will recalculate even if all you did was re-filter the darned sheet. If you’ve got a gazillion dynamic ranges this will make a BIG difference.

@ Debra – Once again, THANKYOUTHANKYOUTHANKYOU!

4. @David, I agree with Lynda — INDEX is non-volatile, and your workbook should perform better if you use it instead of OFFSET.

@Tony, thanks! Functions are your friends too. ;-)

@Lynda, you’re welcome! And thanks for answering David’s question.

5. David Tyler says:

Yes, I forgot to consider the volatility. That would make the difference right there. Well, it looks like I’m a changed man – it’s INDEX for dynamic ranges from here on out. Thanks for the tip.

6. Elias says:

I believe all the dynamic ranges are volatile, so I don’t think they will be a performance benefit from using Index instead of Offset when creating dynamic ranges. However, they will be a better performance when you use
=SUM(A1:INDEX(A:A,C1) over =SUM(OFFSET(A1,,,C1,)).

Regards

7. Gregory says:

There are two forms of the INDEX function: array and reference.

You mention the INDEX function can return a cell reference, yet show the syntax for the Array form, =INDEX(array,row_num,column_num), which only returns a value.

The INDEX function syntax for the Reference form is INDEX(reference, row_num, [column_num], [area_num]) which returns the reference of the cell at the intersection of the row_num and column_num. The Dynamic range you create is receiving a reference from the INDEX function using the Reference form of the function. Correct?

8. Patrick Matthews says:

Deb,

Great post, as usual.

I used to go with COUNTA (which would count non-numerics) when doing this, but more recently I switched to MATCH.

=Rates!\$A\$1:INDEX(Rates!\$2:\$2,1,COUNT(Rates!\$2:\$2))

I would use:

=Rates!\$A\$1:INDEX(Rates!\$2:\$2,1,MATCH(10^200,Rates!\$2:\$2))

If the “last” value is expected to be text instead of numeric or a date:

=Rates!\$A\$1:INDEX(Rates!\$2:\$2,1,MATCH(“ZZZZZZZZZZZZ”,Rates!\$2:\$2))

In the dark days before Tables were available, I often used a dynamic range like this as the basis for a PivotTable, if the values in Col A were numeric/dates:

=Rates!\$A\$1:INDEX(Rates!\$1:\$65536,MATCH(10^200,Rates!\$A:\$A),MATCH(“ZZZZZZZZZZZZ”,Rates!\$1:\$1))

or if Column A had text:

=Rates!\$A\$1:INDEX(Rates!\$1:\$65536,MATCH(“ZZZZZZZZZZZZ”,Rates!\$A:\$A),MATCH(“ZZZZZZZZZZZZ”,Rates!\$1:\$1))

Patrick

9. @Gregory, thanks, I’ve corrected the syntax in the article.

10. Jeff Weir says:

Ha ha…Good post, but especially love the amusing add for Colonix at the top. Pity this post isn’t on data cleansing, Debra ;-)

11. Vinod says:

Hi Debra,

Very good post. The above explained formula has limitation i.e., if Your data contains any blank cell, then the range will be incorrect.

I Prefer to use the below formula – which works even in the Blank cell as well.

=Rates!\$A\$1:INDEX(Rates!\$2:\$2,COUNTA(Rates!\$1:\$1)) – even syntax is bit smaller.

I used the counta for row 1(1:1, heading) because, the ideal table should not contain blank heading, but it may contain blank data.

Vinod

12. Patrick, thanks for posting your examples.

13. @Jeff, I wonder what advertising glitch led to that ad being displayed!

14. @Vinod, thanks for posting your formula, and explaining why you use it.

15. Hugo says:

Fine.
And what if the table takes more then 1 row containing data, may be even with blank cells?
Is there an INDEX solution too?

16. @Hugo, you could use a MATCH function to find the last used cell in a column, as Patrick showed in his comment. Of course, that assumes the last cell in that column won’t be blank!

Or, if you’re using Excel 2007, or later, you could use a named Excel Table.

17. Thara says:

That would make a great caption on a t-shirt. :)

18. Awesome idea! ;-)

Found this after 5 years, still an awesome idea to be used in dynamic named ranges instead of offset :)
Thanks!

1. February 24, 2013

[…] Excel balks at using an entire column in this formula, try a dynamic named range as explained here: Excel Function Friday: INDEX for Dynamic Range | Contextures Blog The basics of named ranges are explained here: Excel Names — Excel Named […]

2. August 29, 2013

[…] are probably better and easy to employ as well (I typically use this as my first goto nowadays): Excel Function Friday: INDEX for Dynamic Range | Contextures Blog Don't overthink the "danger" of using Count(A:A). Excel is optimized to avoid unnecessary […]

3. February 14, 2015

[…] you don’t use a table, seriously consider using and creating dynamic name ranges for each of your fields/columns.  Name ranges or table names is the easiest and best method to use […]