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.

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:

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

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

• 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