Categories
Excel

Nest Dynamic Named Ranges with Match Function

A dynamic named range that resides within another dynamic named range can be very useful for things such as long lists of names.

For example, it’s possible to create a named range called Jnames that refers to all the names in a sorted list beginning with the letter J.

Start with a list of names in column A, such as the ones shown in the figure, where cell A1 is a heading, and the list is sorted:

  1. Navigate to the Formulas tab and click Define Name. This will open the New Name dialog box.
  2. In the Name box, type Names.
  3. In the Refers To box, enter the following formula:
=OFFSET($A$2,0,0,COUNTA($A$2:$A$1000),1)

Click OK.

Open the New Name dialog box again and in the Name: box enter the name Jnames (J can be any desired letter).

In the Refers To: box, enter the following:

=OFFSET(INDIRECT(ADDRESS(MATCH("J*",Names,0)+1,1)),0,0,COUNTIF(Names,"J*"),1)

where “J*” is a match for the data you want, in this case, names beginning with J). Now click OK.

Press CTRL+F3, select Jnames in the Name Manager and click in the Refers to: box where the function is, all the names beginning with the letter J will have a marquee around them, as shown in the figure.

If you want, you can create one named range for each letter of the alphabet, but perhaps a better option is to have the named range change according to a letter that you type into a cell on a worksheet. To do this:

  1. Enter B into C2 cell and then name that cell Letter.
  2. Open the New Name dialog box.
  3. In the Name: box and type LetterNames.
  4. In the Refers To: box, enter the following formula, and when you’re done, click OK:
=OFFSET(INDIRECT(ADDRESS(MATCH(Letter&"*",Names,0)+1,1)),0,0,COUNTIF(Names,Letter&"*"),1)

To test this, enter letter B into the cell you named Letter (in our case C2), and enter =LetterNames into the cell D2, you should see data starting with the letter B, as shown in the figure: