VLOOKUP Table Array (Table of Contents)
Introduction to VLOOKUP Table Array
Vlookup Table Array is used for finding and looking up the required values in the form of table array. And Table Array is the combination of two or more than two tables which has data and values linked and related to one another. Although headers may be a 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. Let’s understand how to use the VLOOKUP Table Array in Excel with some examples.
Example #1 – Mapping and Creating Table
The first table has the Owner’s name and the quantity sold by them for the respective product class. And the second table has the range of quantity sold with the incentive which is allotted to different quantity sold range as shown below.
And with the help of this data, we need to map and create another table in the respective column in 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 Product class as shown below.
Drag the same formula in cell C2 to cell 9.
For Range and Incentive columns, we need to map the existing data 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 sources of data 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 Vlookup Argument Box as shown below.
- Lookup_value = Lookup value is selected as C2 of the same table where array lookup is being applied.
- Table_Array = Table Array is Table 2 which is 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 variable data range. For this select “1” or “TRUE”. This gives not the exact result but the result from variable data range.
Once done, click on Ok to see the result.
Once done, drag the applied formula to the below respective cells as shown below.
Now for calculating the actual incentive, multiple the Range columns with 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 respective below cells as shown below.
Example #2 – VLOOKUP Table Array
For this, we will consider the same data which we have seen in example 1.
We can name the table as Table 1 as per heading and second table as Table 2 as shown below. This will allows us to see the table name in Vlookup syntax when we will select the table range.
Now go to the cell where we need to see the output for product and type “=VLOOKUP” and select the function as shown below.
Now select Owner Name as Lookup_Value as E3 which is 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 the product no. 345. We will see Vlookup with Table Array has fetched the value of 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 the 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 that it doesn’t have any value related to product number 881 in Table 1.
Pros & Cons of Vlookup Table Array
- Data from different tables that are liked and related to each other can be mapped with a single table.
- Example 2 is easy and simple to use.
- Naming the table before applying the formula makes syntax small.
- We can use more any number of table array 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 with each other in terms of data.
- For Table Array table, always use the reference of that lookup value which related to 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 along with practical examples and downloadable excel template. You can also go through our other suggested articles –
