How to Name a Table Array in Excel (With Easy Steps)

This tutorial will demonstrate how to name a table array in excel. It is a very important argument in the LOOKUP Function. In case, you want to find a certain column or group of columns in the worksheet, then naming the table array is very useful. After naming a table array, you can find it with selected cells under it only by searching by the proper name. So, it shortens our work and is essential for us to learn using it quickly.

Download Practice Workbook

You can download the practice workbook from here.

What Is a Table Array?

Table Array is one of the most important arguments of any LOOKUP function such as the VLOOKUP function or the HLOOKUP function. Table array is mainly used to find specific data or formulas or groups of formulas or data. If you use this function, it will search the particular data you have inserted. So, giving the name of the table array can be helpful for simplifying the work.

Step-by-Step Procedures to Name a Table Array in Excel

If you follow the steps correctly, you should learn how to name a table array in excel on your own. The steps are:

Step 1: Creating Table Array

At the very beginning, we will create a table array using the VLOOKUP Function. The VLOOKUP Function is mainly used when you have to find a table or some info or any data by row. The process of this step is described below.

• First, arrange the proper dataset. In this case, we have the Salesperson in Column B and two years 2021 and 2022 in Column C and D.

• Next, in cell D14 insert the following formula.

• After that, you will get the desired result.

Read More: What Is a Table Array in VLOOKUP? (Explained with Examples)

Step 2: Naming Table Array

After creating the table array, now our target is to name it properly. For naming the table array, we will follow the below process.

• At first, select the desired column( in this case Column B).
• Then go to Formulas > Defined Names options.

• Second, the New Name dialog box will come on the screen.
• Next, give a suitable name and select the cells you want to use, and press OK.

• Last, if you write the name, you will get the data like the below image.

Read More: How to Find Table Array in Excel (4 Suitable Examples)

Similar Readings

• How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
• Excel VBA: Remove Duplicates from an Array (2 Examples)
• VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
• How to Use VLOOKUP Table Array Based on Cell Value in Excel
• Excel VBA to Read CSV File into Array (4 Ideal Examples)

Step 3: Creating Dynamic Named Range

Dynamic ranges are also known as expanding ranges. Dynamic ranges normally expand automatically and connect with the most suitable data. In this case, we will use the OFFSET function to create the dynamic range. The main thing about the OFFSET function is, that it can recalculate all data or formulas after making any change in the worksheet. The steps are.

• To begin with, go to select range > Formulas > Defined Names.

• In addition, in the New Name dialog box, insert the following formula and press OK.

• Furthermore, if you write the name in any cell, you will find the data under this data like in the previous step.

Read More: How to Convert Range to Array in Excel VBA (3 Ways)

Step 4: Editing Named Ranges

When dealing with a certain project, it is very obvious that you need to edit or delete or add new data in the named ranges. To do so, we have to follow the below steps.

• Firstly, go to select range > Formulas > Defined Names>New Manager options.

• Secondly, the New Manager dialogue box will open up on the screen. You can use New or Edit or Delete according to your need.

• Thirdly, if you want to edit then click the Edit option and the Edit Name option will come on your screen. You can make any change in this Name or Refers to options to get the desired result.

• Lastly, if you want to delete something use the Delete option and choose OK like the below image.

Read More: How to Edit Table Array in Excel (2 Suitable Ways)

How to Name an Array Constant

Array constants are a set of values that normally come with curly brackets {}. Now, we want to name an array constant in the following order.

Steps:

• At first, go to select range > Formulas > Defined Names.
• Second, the New Name dialog box will come on the screen. Name it as you want and select the desired cells and press OK.

• Last, you will get the desired result.

Things to Remember

• The main focus is to create the table array at first. So, the first step should be done carefully.
• In the case of naming, if there remains any space between words then you have to use “_” in between the words. Otherwise, it won’t work.
• Only Edit or Delete or New options, if it is very necessary.
• In the case of Naming an array constant, the constants must be kept in curly brackets {}.

Conclusion

Henceforth, follow the above-described methods. Thus, you will be able to name a table array in excel. Let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.

Related Articles

• Excel VBA to Create Data Validation List from Array
• How to Expand Table Array in Excel (5 Simple Ways)
• Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
• How to Lock Table Array in Excel (2 Effective Ways)
• Sort Array with Excel VBA (Both Ascending and Descending Order)
• How to Split a String into an Array in VBA (3 Ways)
• Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)