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