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.

**Table of Contents**hide

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

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

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

- 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)`

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

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

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)`

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

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

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**

**Excel Dynamic Range Based On Cell Value****Create a Dynamic Chart Range in Excel (2 Methods)****How to Use Dynamic Range for Last Row with VBA in Excel (3 Methods)**

### 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)`

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

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

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)))`

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

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

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))`

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

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

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)`

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

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.

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

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

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