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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.


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

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.

Procedures to Name a Table Array

  • Next, in cell D14 insert the following formula.
=VLOOKUP(B14,B5:D11,2,0)

Procedures to Name a Table Array

  • After that, you will get the desired result.

Procedures to Name a Table Array


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.

Procedures to Name a Table Array

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

Procedures to Name a Table Array

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

Procedures to Name a Table Array

Read More: How to Find Table Array in Excel


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.

Procedures to Name a Table Array in Excel

  • In addition, in the New Name dialog box, insert the following formula and press OK.
=OFFSET(C5,0,0,COUNTA(C:D),1)

Procedures to Name a Table Array in Excel

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

🔎 How Does the Formula Work?

  • COUNTA(C:D): It represents the selected columns of the worksheet.
  • OFFSET(C5,0,0, COUNTA(C: D),1): In this portion, the first value is the first selected cell, the COUNTA function shows the selected columns and the last value presents the cell’s necessary width.

Read More: How to Expand Table Array in Excel


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.

Procedures to Name a Table Array in Excel

  • 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


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

Download Practice Workbook

You can download the practice workbook from here.


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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Table Array in Excel | Excel VLOOKUP Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Zehad Rian Jim
Zehad Rian Jim

Zehad Rian Jim is a dedicated professional with a BSc. Degree in Naval Architecture and Marine Engineering from Bangladesh University of Engineering and Technology. He's good with Microsoft Office and has written more than 80 helpful articles for ExcelDemy in the last year. Now, he makes fun and easy-to-follow Excel tutorials for YouTube as part of the ExcelDemy Video project. Zehad loves figuring out Excel problems, and his passion for learning new things in Excel shines through in... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo