While using the **VLOOKUP **or **HLOOKUP **function, there is a mandatory table array argument where we set the range for looking up values. So to learn how to create a table array argument in Excel, this article will provide you with 3 useful examples with sharp steps.

**Table of Contents**hide

**Download Practice Workbook**

You can download the free Excel template from here and practice on your own.

**What Is Table Array in Excel**

When we use a **VLOOKUP **or **HLOOKUP **function then we enter a range of cells i.e. **B5:C7** in the formula. And that range is called the **table_array** argument. The Lookup functions search for criteria to match within this array.

In the above picture, the **VLOOKUP **function searches for a match of value in **B10 **within the range **B5:C7**. **B5:C7** is the table array argument here.

**3 Methods to Create a Table Array in Excel**

To demonstrate the examples, we’ll use the following dataset that represents some salespersons’ sales in two consecutive years.

**1. Create a Table Array for VLOOKUP Function in Excel**

In our very first example, we’ll learn how to create a table array for **the VLOOKUP function**.

**Steps:**

**Type**the following formula in**Cell D14**:

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

- Then
**press**the**Enter**button.

Have a look that the lookup range **B5:D11** is the table array argument.

**Read More:** **How to Use VLOOKUP Table Array Based on Cell Value in Excel**

**2. Mapping and Creating Table for VLOOKUP Function**

For this method, we’ll create multiple table arrays to use **the VLOOKUP function. **That’s why I have modified the dataset. The first table shows the salespersons’ sold quantity and the second table represents the bonus percentage according to quantity range. First, we’ll set the named range for each table.

**Steps:**

- Select the data from the first table.
- Then just type the name in the cell reference box and press
**Enter**.

- Like the same procedure, set the name for the second table.

Now we’ll look up the quantity, range, and bonus for a particular salesperson.

- In
**Cell C12**, write the following formula-

`=VLOOKUP(B12,Table_1,2,0)`

- Later, press the
**Enter**button to get the quantity for**Luka**.

- Drag down the
**Fill Handle**icon to copy the formula for**Aron**and**Leo**.

Now we’ll find the bonus percentage according to the quantity range. As the quantity is not exact to the range that’s why we used the approximate match in the **VLOOKUP **function.

- In
**Cell D12**, type the following formula-

`=VLOOKUP(C12,Table_2,2,1)`

**Hit**the**Enter**button.

- Again use the
**Fill handle**tool to copy the formula.

Finally, we’ll find the bonus amount.

**Write**the following formula in**Cell E12**–

`=C12*D12`

- After that, hit the
**Enter**button to get the output.

- Then use the
**Fill Handle**tool to finish.

Now have a look, the bonus amount is evaluated using two table arrays.

**Read More: ****How to Name a Table Array in Excel (With Easy Steps)**

**Similar Readings**

**Make Group by Same Interval in Excel Pivot Table (2 Methods)****How to Find Table Array in Excel (4 Suitable Examples)****Create a Table in Excel with Data (5 Ways)****What Is a Table Array in VLOOKUP? (Explained with Examples)****How to Insert A Pivot Table in Excel (A Step-by-Step Guideline)**

**3. Use VLOOKUP Function by Creating Variable Table Array in Excel**

Lastly, we’ll learn to create the variable table array for the **VLOOKUP **and **INDIRECT **functions**. **For that, I have modified the dataset again and made two tables to show the sales for two months. First, we’ll set named ranges for the tables.

For the data range of the first table, I named **March**.

And named **April** for the second table.

Now let’s use these table arrays to get the sales of a salesperson from the two table arrays.

**Steps:**

- Write the following formula in
**Cell E12**–

`=VLOOKUP(B12,INDIRECT(D12),2,0)`

- Then just
**hit**the**Enter**button.

- Finally, drag down the
**Fill Handle**icon to copy the formula.

Soon after, you will get the output like the image below.

**Read More: ****How to Use VLOOKUP Table Array Based on Cell Value in Excel**

**Pros & Cons of Using Table Array in VLOOKUP**

- You can map with a single table if the data is from individual tables that are linked and related between them.
- Before using the formula, Giving names for the table can make the syntax small.
- More table arrays can be used for the
**VLOOKUP**function. - If the tables are not connected then no need to use the table array in
**VLOOKUP.**

**Things to Remember**

- It is useful to use
**VLOOKUP**Table Array if the tables are co-related with each other. - The Table Array must be more than 2 tables.

**Conclusion**

I hope the procedures described above will be good enough to create a table array in Excel. Feel free to ask any question in the comment section and please give me feedback.

**Related Articles**

**How to Lookup a Table and Return Values in Excel (3 Simple Ways)****Edit Table Array in Excel (2 Suitable Ways)****[Fix] The Pivot Table Name Is Not Valid (7 Causes with Solutions)****How to Expand Table Array in Excel (5 Simple Ways)****Get a Count in Excel Pivot Table Calculated Field****How to Lock Table Array in Excel (2 Effective Ways)****Types of Tables in Excel: A Complete Overview**