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

Create a Table Array for VLOOKUP Function in Excel

Have a look that 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 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.

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.


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.


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.

Mithun

Mithun

Hello! I am Md. Sourov Hossain Mithun. Welcome to my profile. Currently, I am working at Exceldemy as an Excel and VBA Content Developer. Excel is amazing software. 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

ExcelDemy
Logo