In this article, we will provide 3 useful examples of how to create and use a table array.

**Watch Video – Create a Table Array in Excel**

**What Is Table Array in Excel**

When we use a **VLOOKUP **or **HLOOKUP function**, we enter a range of cells in which to look up the required value, for example **B5:C7 **in the dataset below. This range is called the **table_array** argument.

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

**Create a Table Array in Excel: ****3 Examples**

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

**Example 1 – Create a Table Array for VLOOKUP Function**

First we’ll create a table array with a single table for **the VLOOKUP function**.

**Steps:**

- Enter the following formula in
**Cell D14**:

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

- Press
**Enter**.

The lookup range **B5:D11** is the table array argument.

**Read More:** Types of Tables in Excel: A Complete Overview

**Example 2 – Mapping and Creating Table for VLOOKUP Function**

Now we’ll create multiple table arrays for use with the **VLOOKUP** function. To illustrate, the dataset has been modified so that 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.
- Type a name for the selected range in the cell reference box and press
**Enter**.

- Repeat the procedure to set a name for the second table.

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

- In cell
**C12**, enter the following formula:

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

- Press
**Enter**to return 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 in the range, we use the Approximate Match option in the **VLOOKUP **function by setting **1** as the fourth argument.

- In cell
**D12**, type the following formula:

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

- Press
**Enter**.

- Use the
**Fill handle**tool to copy the formula to the cells below.

Finally, we’ll find the bonus amount.

- Enter the following formula in cell
**E12**:

`=C12*D12`

- Press
**Enter**to return the output.

- Use the
**Fill Handle**tool to fill the cells below.

The bonus amount is evaluated using two table arrays.

**Example 3 – Creating a Variable Table Array**

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

**Steps:**

- Name the data range of the first table
*March*.

- Name the second table
*April*.

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

- Enter the following formula in cell
**E12**:

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

- Press
**Enter**.

- Drag down the
**Fill Handle**icon to copy the formula to the cells below.

The output is as in the image below.

**Read More:** Excel Table vs. Range: What Is the Difference?

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

- Map with a single table if the data is from individual tables that are linked and related.
- If the tables are not connected, then there is no need to use a table array in
**VLOOKUP**. - Before using the formula, providing names for the table can simplify and reduce the syntax.
- More table arrays can be used for the
**VLOOKUP**function.

**Things to Remember**

- It is useful to use
**VLOOKUP**Table Array if the tables are co-related to each other. - The Table Array must consist of 2 or more tables.

**Related Articles**

- How to Convert Range to Table in Excel
- Navigating Excel Table
- How to Make Excel Tables Look Good
- How to Convert Table to List in Excel
- Table Name in Excel: All You Need to Know
- How to Provide Table Reference in Another Sheet in Excel
- How to Insert Floating Table in Excel
- How to Make a Comparison Table in Excel

**<< Go Back to Excel Table | Learn Excel**