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.

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.

Next, in cell D14 insert the following formula.

=VLOOKUP(B14,B5:D11,2,0)

After that, you will get the desired result.

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

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.

=OFFSET(C5,0,0,COUNTA(C:D),1)

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.

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.

Name a Table Array.xlsx

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.

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

Get FREE Advanced Excel Exercises with Solutions!