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

Get FREE Advanced Excel Exercises with Solutions!

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.

Watch Video â€“ Create a Table Array in Excel

## 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:
`=VLOOKUP(B14,B5:D11,2,0)`
• Then press the EnterÂ button.

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

### 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-
`=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: 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â€“
`=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.

## 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.

## 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.

## What is ExcelDemy?

ExcelDemy Learn Excel & Excel Solutions Center provides free Excel tutorials, free support , online Excel training and Excel consultancy services for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Tags:

Md. Sourov Hossain Mithun

Hello! I am Md. Sourov Hossain Mithun. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is an amazing software to learn or work. Here, I will post Excel related useful articles. I am a graduate of Bangladesh University of Engineering and Technology. I love to learn new things and work with them. Thank you.

We will be happy to hear your thoughts

Advanced Excel Exercises with Solutions PDF