How to Create a Table Array in Excel (3 Methods)

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.

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.

Create a Table Array in Excel: 3 Methods

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:

• Then press the Enter button.

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

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

2. Mapping and Creating Table for VLOOKUP Function

For this method, we’ll create multiple table arrays using 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-

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

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

• 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: Does TABLE Function Exist in Excel?

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–

• 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: Excel Table vs. Range: What Is the Difference?

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 there is 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 to each other.
• The Table Array must be more than 2 tables.

Download Practice Workbook

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

Conclusion

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

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