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.

Create a Table Array for VLOOKUP Function in Excel

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.

Mapping and Creating Table for VLOOKUP Function

  • Like the same procedure, set the name for the second table.

Mapping and Creating Table for VLOOKUP Function

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.

Mapping and Creating Table for VLOOKUP Function

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

Mapping and Creating Table for VLOOKUP Function

  • Again, use the Fill handle tool to copy the formula.

Mapping and Creating Table for VLOOKUP Function

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.

Mapping and Creating Table for VLOOKUP Function

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

Use VLOOKUP Function by Creating Variable Table Array in Excel

And named April for the second table.

Use VLOOKUP Function by Creating Variable Table Array in Excel

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.

Use VLOOKUP Function by Creating Variable Table Array in Excel

  • Finally, drag down the Fill Handle icon to copy the formula.

Use VLOOKUP Function by Creating Variable Table Array in Excel

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

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

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo