Updated August 10, 2023
Introduction to VLOOKUP Table Array
Vlookup Table Array is used for finding and looking up the required values in a table array. And Table Array is the combination of two or more two tables which has data and values linked and related to one another. Although headers may be quite different relation of those data with each other will be seen.
How to Use VLOOKUP Table Array in Excel?
VLOOKUP Table Array in Excel is very simple and easy. With some examples, let’s understand how to use the VLOOKUP Table Array in Excel.
Example #1 – Mapping and Creating Table
The first table has the Owner’s name and the quantity they sell for the respective product class. And the second table has the range of quantity sold with the incentive allotted to different quantity sold ranges as shown below.
And with the help of this data, we need to map and create another table in the respective column in the below table.
Now go to the cell where we need to see the result and select the Insert Function option, which is just beside the Formula Bar.
We will get the Insert Function box. Now from there, under Select for a function: window, type ALL or search VLOOKUP as shown below. After that, click on Ok.
After that, we will get the Function Arguments box of Vlookup, as shown below. Select the range from Table 1 and click on Ok once done.
Now drag the applied Vlookup formula to the below cells as shown below.
Do the same procedure for the Product class as shown below.
Drag the same formula in cell C2 to cell 9.
We need to map the existing data for Range and Incentive columns, which we have just looked up from Table 1 with the data available in Table 2. This process of looking up the values from the second of different data sources with already mapped data is called Vlookup Table Array. For this, go to the first cell of the Range column and click on Insert Function to open the Vlookup Argument Box, as shown below.
- Lookup_value = Lookup value is selected as C2 of the same table where array lookup is applied.
- Table_Array = Table Array is Table 2, shown in the above screenshot.
- Col_Index_Num = It is selected as 2 as the second column of Table 2.
- Range_lookup = Here, the Range column of Table 1 has a variable data range. For this, select “1” or “TRUE”. This gives not the exact result but the result from the variable data range.
Once done, click on Ok to see the result.
Once done, drag the applied formula to the respective cells below.
To calculate the actual incentive, multiply the Range columns by the Quantity Sold. This will show how much the Incentive will be given to that owner for the extra quantity he/she sold. Now drag the applied formula to the respective below cells as shown below.
Example #2 – VLOOKUP Table Array
For this, we will consider the same data we saw in example 1.
We can name the table Table 1 as per the heading and the second table Table 2 as shown below. This will allow us to see the table name in Vlookup syntax when selecting the table range.
Now go to the cell where we need to see the output for the product and type “=VLOOKUP,” and select the function as shown below.
Now select Owner Name as Lookup_Value as E3, our product number column.
Table_Array as complete Table_1 data from cell A3 to C9.
Col_Index_Num as 2, which is Product in Table_1.
Range_Lookup as “0” for the exact value.
Press Enter key to exit from the syntax.
Now in cell E3, insert any product no. Let’s enter product no. 345. We will see Vlookup with Table Array has fetched 345 in cell F3, as shown below.
Let’s test if this can be applicable for the rest of the product, no. or not. For that, we have added some product numbers below cell E3, as shown below.
Once done, drag the formula to below respective cells till the product numbers are there. We will see Vlookup has fetched the values for all most all the product numbers in column F. But cell E6 has shown error #N/A, which means it doesn’t have any value related to product number 881 in Table 1.
Pros & Cons of Vlookup Table Array
- You can map data from multiple related tables to a single table using Vlookup.
- Example 2 is easy and simple to use.
- Naming the table before applying the formula makes syntax small.
- We can use more of any number of table arrays for Vlookup.
- It does not make any sense to use a Vlookup table array where tables are not related to each other.
Things to Remember
- It is recommended to use Vlookup Table Array, where tables are co-related in terms of data.
- For the Table Array table, always use the reference of that lookup value related to the Array Table.
- Table Array should be more than 2 tables.
Recommended Articles
This is a guide to Vlookup Table Array. Here we discuss how to use Vlookup Table Array, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –
