Dynamic Named Range Based on Cell Value in Excel

When working with a continuously changing data set, we may need to make our named range dynamic so that it automatically increases to accommodate newly combined entries or records to exclude eliminated data. In this article, we will see how to do a dynamic named range based on cell value in Excel.


Download Practice Workbook

You can download the workbook used for the demonstration from the link below.


5 Easy Ways to Create Dynamic Named Range Based On Cell Value in Excel

Now we will go through a total of five different methods to create a dynamic named range based on cell value in Excel. The basic idea to name a range is the same- we will use the Name Manager from the Formula tab. In general, we can select a range from a spreadsheet and name it after that through this feature. Or we can select a formula to do that. As the former would result in a fixed range, it would not serve our purpose. So we will resort to using formulas to create dynamic named ranges in Excel. These methods differ only in the formulas we are using.

For each method, we are going to use the same dataset.

dataset for excel dynamic named range based on cell value


1. Create Dynamic Named Range Based On Cell Value with Excel OFFSET Function

In the first method, we are going to be using the OFFSET function only for the formula. This function takes three primary arguments- a reference, and the number of rows and columns it will deviate. It can also take the number of rows and columns it will return as its 4th and 5th arguments, although they are not mandatory. It then returns a value or an array depending on the number of values and their type.

So we can easily use this function to create a dynamic named range based on cell value in Excel. Follow these steps for a detailed guide.

Steps:

  • First, go to the Formulas tab on your ribbon.
  • Then select Name Manager from the Defined Names group section.

name manager in the ribbon

  • As a result, the Name Manager box will appear.
  • Now click on the New button on the top left corner of the box.

creating new name for ranges

  • Next, insert a name in the Name field of the Edit Name We are choosing Device_Models as the name.
  • Then in the Refers to field, insert the following formula.

=OFFSET(OFFSET!$D$4,1,0,COUNTA(OFFSET!$D$5:$D$1000),1)

excel dynamic named range based on cell value using offset function

  • Finally, click on OK and close the Name Manager The naming of the range is now complete.
  • To test the range, go to the spreadsheet and type out the following.

=Device_Models

  • Then press Enter. You will find that the range will appear.

excel dynamic named range based on cell value for offset function output

  • If we add a value now, it will automatically update the output.

excel dynamic named range based on cell value for offset function test

This is one way we can name a dynamic range in Excel.


Read More: OFFSET Function to Create & Use Dynamic Range in Excel


2. Combine OFFSET and MATCH Functions to Create Dynamic Named Range

Another way, you can achieve a similar result is by a combination of the OFFSET and MATCH functions. The OFFSET function gives us an output depending on the set of variables and their nature. Meanwhile, the MATCH function returns the match of a value in an array. So we can create a formula out of those which can identify a set of numbers that we can use as the range. Keep in mind, this number identifying and isolation is based on a cell value and so we are creating a dynamic named range in Excel this way.

Follow these steps to see how we use the formula and apply it.

Steps:

  • First, go to the Formulas ribbon on your tab.
  • Then select Name Manager from the Defined Names group section.

  • As the Name Manager box pops up, select New in the top-left corner of the box.

  • In the next box, fill the Name field with the name you want.
  • Also, insert the following formula in the Refers to field beneath.

=OFFSET('OFFSET,MATCH(num)'!$E$4,1,0,MATCH(9.99999999999999E+307,'OFFSET,MATCH(num)'!$E$5:$E$1000),1)

excel dynamic named range based on cell value using offset and match for numbers

  • Then click on OK and close the Name Manager The dynamic named range based on cell value is now complete.
  • To test the result, let’s go back to the Excel spreadsheet and insert the following formula.

=Device_Prices

  • After that, press Enter.

excel dynamic named range based on cell value using offset and match for numbers output

  • Now insert a value under the column and you will see the output change accordingly.

excel dynamic named range based on cell value using offset and match for numbers test

Thus, we have created a dynamic named range based on cell value in Excel.

Read More: Excel VBA: Dynamic Range Based on Cell Value (3 Methods)


Similar Readings


3. Generate Named Range for Text Values by Joining OFFSET and MATCH Functions

We can also use the same OFFSET and MATCH functions to identify text values and thus create a dynamic named range. Usage of this formula in Excel can also be included in the dynamic named range based on cell value criteria.

Follow these steps to see how we can achieve that.

Steps:

  • First of all, go to the Formulas tab on your ribbon.
  • Now select Name Manager from the Defined Names group section.

  • After that, click on the New button on the top-left of the dialog box.

  • Then enter a name in the Name field in the following box.
  • After that, insert the following formula in the Refers to field to define the range.

=OFFSET('OFFSET,MATCH(text)'!$C$4,1,0,MATCH(REPT("z",255),'OFFSET,MATCH(text)'!$C$5:$C$1000),1)

excel dynamic named range based on cell value using offset and match for text

  • Now click on OK and close the Name Manager The creation of an Excel dynamic named range based on cell value for texts is thus complete.
  • Now to test it out go back to the spreadsheet and type the following in a cell.

=Device_Type

  • And press Enter.

excel dynamic named range based on cell value using offset and match for text output

  • Finally, let’s enter a value under the column we are filtering from and the output should change accordingly.

excel dynamic named range based on cell value using offset and match for text test

This is how we can create a dynamic named range based on text cell value in Excel.

Read More: Create Dynamic Named Range with VBA in Excel (Step-by-Step Guideline)


4. Apply FILTER Function to Create One Dimensional Dynamic Named Range in Excel

Another way we can create a dynamic range is to use the FILTER function in the reference box. This function takes a range or an array as the first argument and filters values from them depending on the second argument.

Follow these steps to see how we can use this to our advantage to create the dynamic named range in Excel.

Steps:

  • First, go to the Formulas tab on your ribbon.
  • Then select Name Manager from the Defined Names group section.

  • Now click on the New button on the top-left of the Name Manager box that will pop up next.

  • Consequently, insert the name in the Name field of the following box.
  • Then insert the formula in the Refers to box as the reference.

=FILTER(FILTER!$D:$D,((FILTER!$D:$D<>"")*(FILTER!$D:$D<>FILTER!$D$4)))

excel dynamic named range based on cell value using filter function

  • After clicking on OK here, close the Name Manager box also. At this point, the dynamic named range is complete.
  • Now to test it out, go back to the spreadsheet and insert the following formula.

=Device_Models_2

  • After that, press Enter.

excel dynamic named range based on cell value using filter function output

  • Now insert something under the column we are picking reference from and it will update the output.

excel dynamic named range based on cell value using filter function test

It is another way to create a dynamic named range that can also be based on cell value in Excel.

Read More: How to Use Dynamic Range VBA in Excel (11 Ways)


5. Create Two Dimensional Dynamic Named Range in Excel

All of the ranges we have created up until now are single-dimensional arrays (one-column values). We can create such references using the INDIRECT, COUNTA, and ROW functions.

The INDIRECT function provides us with a reference from a string it takes as an argument. Meanwhile, the COUNTA function returns the non-empty cells from a range. Whereas, the ROW function returns the row number from the reference argument.

Here is how we can create a two-dimensional dynamic named range in Excel with the functions.

Steps:

  • First, go to the Formulas tab on your ribbon.
  • Second, select Name Manager from the Defined Name group section.

  • Third, click on New from the top-left of the Name Manager box that pops up.

  • Next, insert a name in the Name field for the range.
  • Also, insert the following formula in the Refers to field below.

='2D'!$B$5:INDIRECT("C"&COUNTA('2D'!$C:$C)-1+ROW('2D'!$C$4))

excel dynamic named range based on cell value for two dimensional arrays

  • Then click on OK and close the Name Manager box after that.
  • To test if the formula is working or not, go back to the spreadsheet and write down the following in a cell.

=Two_Dimensional_Array

  • After pressing Enter you will find something like this.

excel dynamic named range based on cell value for two dimensional arrays output

  • Finally, let’s test the value by inserting two values under the two columns we are taking for the two-dimensional arrays.

excel dynamic named range based on cell value for two dimensional arrays test

As you can see, the output has automatically been updated. So we can say we have created a dynamic named range in Excel as a result.

Read More: Create Dynamic Sum Range Based on Cell Value in Excel (4 Ways)


Insert INDEX Function in Excel for Creating Named Range Based On Cell Value

We can also use the INDEX function to use as a reference. But unlike the previous ones, we will not get a dynamic output range as the result. Although we will get the output range depending on the formula. This function takes the intersection or rows and col it counts as a specific column (which it takes as the first argument).

To see how we can achieve that, you need to follow the steps below.

Steps:

  • First, select the Formulas tab to find the Name Manager which you can find from the Defined Names group.

  • Now click on the New button at the top left of the popped-up box.

  • Now insert a name in the Name box.
  • Then insert the following formula in the Refers to field below that.

=INDEX(Sheet6!$B$5:$B$15,0,0)

excel dynamic named range based on cell value using index function

  • Then click on OK.
  • After that, close the Name Manager box also.
  • Now go back to the spreadsheet and insert the following formula to test it out.

=Device_Brands

  • Finally, press Enter on your keyboard.

excel dynamic named range based on cell value using index function output

As a result, we can create and use the named range in Excel.


Use Name Cell in Excel to Generate Named Range

The simplest way to create a named range is to use the Name Cell feature of Excel. You can find it on the left side of the formula box on the spreadsheet.

Now, follow the steps below to perform the task.

Steps:

  • To use this feature, select the range you want to name and manually edit this.

excel dynamic named range based on cell value using name cell

  • Again, if you want to recall the range, just use the name you have given and you can find so.


Define Names from Selected Range

There is yet another feature to define a name for a range. For that purpose, learn the following process.

Steps:

  • Firstly, you have to select a range.

  • Then, select the Create from Selection feature from the Defined Names group of the Formulas tab.

define names from selected range

  • In the consequent box, select the option that matches your criteria. In this case, it is the Top row.

  • Now, you can find the range through the name of the top row as shown in the following figure.

define names from selected range output

We can also use this method to define names from a selected range in Excel.


Conclusion

So these were all the methods to create named ranges, both dynamic and static based on the cell value in Excel. Hopefully, you have grasped the idea of using these methods and can use them accordingly. I hope you found this guide helpful and informative. If you have any questions or suggestions let us know in the comments below.

For more guides like this visit Exceldemy.com.


Further Readings

Md. Abdullah Al Murad

Md. Abdullah Al Murad

Hello! Welcome to my Profile. Currently, I am working and researching Microsoft Excel, and here I will be posting articles related to this. My last educational degree is BSc, and my program was Computer Science and Engineering from American International University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. Always try to gather knowledge from various sources and try to make innovative solutions.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo