Dynamic Named Ranges & Index Match

Back in the old days before Lists received its revamp to Tables we had another way to ensure our formula was efficient and these are known as Dynamic Named Ranges.

Throwing Dynamic Named Ranges in Google will result in finding many many examples. Most of which will use the OFFSET COUNTA method. There are however, two key reasons I avoid that method.

1) COUNTA can return the incorrect Last Row if you have non-contiguous data (doesn't touch or has blanks in) as it returns the number of populated cells within the range.

2) My near total avoidance of Volatile functions (see here for some insight as to why).

My method of choice is to use a binary MATCH within INDEX. Before I show you how to use the method I need to make it perfectly clear that greater minds than mine came up with this method, i'm simply the preacher.

OK, so the first thing we need to do are to set up BigText & BigNum as Names within Name Manager:

As you can see above they are defined as:

BigNum
=9.99999999999999E+307*1.79769313486231

BigText
=REPT("Z",255)

In short these these represent a 255 character string and an excessively large number. The reason we have both is that the range/array we are referencing will either contain strings of data or numeric values. Depending on which data type the column contains will determine which we use to match against.

As you can see below, my product data contains a Unique Key that is (in theory) derived from a system and is therefore most likely to be a good choice to find the end of my dataset. So ProductID is the column I'll use as my lead column and due to it being right aligned we can determine it to contain numeric values and thus will use BigNum.

So, to create a Dynamic Name that represents a Range of Cells that contains all of the cells in the ProductID field on my Products tab I'd need to go into the Name Manager on the Formulas tab on the Ribbon and click New, give it a name and enter the following in the Refers To box:

=Products!$A$2:INDEX(Products!$A:$A,MATCH(BigNum,Products!$A:$A))

My first Reference is Products!$A$2, which is obviously the cell reference for A2 on my Products tab which is where my ProductID's begin.

This is then followed by a colon ":" operator which we use regularly when creating a Range in our formula (eg. A2:A10).

And finally we define where our range is going to end. To begin we enter INDEX(Products!$A:$A, which is stipulating the Value or Range we are seeking will be found somewhere in Column A. INDEX is made up of 3 parameters ([ ] represents an optional parameter):

INDEX(array, row_num, [column_num]) 
INDEX(reference, row_num, [column_num])

So back to our target formula, you will see in this instance we are only using the first two parameters - reference and row_num and we are going to use a Binary Search to determine where our data ends MATCH(BigNum,Products!$A:$A) which in short is looking for our very large number (BigNum) in Column A which as designed will never be found and the result (I'm not explaining how in this article) will be the final used cell in my Reference parameter. If my data ends in Row 101 (as it does in the example workbook) my DNR will encompass A2:A101 but the second I confirm a value in A102 (or below) my Name will automatically resize and any formula using the Name will dynamically correct themselves to allow for the additional data. Go into Name Manager at any time, click in the Refers To section of a Name it will highlight the range the DNR is referencing. Add to the list, go back in and you'll see it's already picked up your additional record.

Some things to keep in mind:

1) If your data table has the capacity to be dynamically emptied you may want to wrap a MAX function around the MATCH(....) element to ensure the 2nd Operand (Right side of the Colon) can never be set to 1 (header row), especially when you're clearing the Range.

=Products!$A$2:INDEX(Products!$A:$A,MAX(2,MATCH(BigNum,Products!$A:$A)))

2) Yes, you can check for both Strings & Numerics in one hit, it's however a rather long formula:

=Products!$A$2:INDEX(Products!$A:$A,MAX(IF(ISNUMBER(CHOOSE({1;2},
MATCH(BigNum,Products!$A:$A,1),MATCH(BigText,Products!$A:$A,1))),
CHOOSE({1;2},MATCH(BigNum,Products!$A:$A,1),MATCH(BigText,Products!$A:$A,1)))))

3) If your Data has 100 columns and thought of doing this 100 times is daunting, then yeah, totally agree. On the link below is a very simple DNR Maker I created that will happily do all 100 in a flash for you (with the more complex formula variant) providing your field headers meet the criteria laid out when defining names (see here)

For a working example of DNR's in action or to download a copy of my DNR Maker Add-In please see here.