EDUCBA Logo

EDUCBA

MENUMENU
  • Explore
    • EDUCBA Pro
    • PRO Bundles
    • Featured Skills
    • New & Trending
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign Up
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP Table Array
 

VLOOKUP Table Array

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

VLOOKUP Table Array

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.

Watch our Demo Courses and Videos

Valuation, Hadoop, Excel, Mobile Apps, Web Development & many more.

You can download this VLOOKUP Table Array Template here – VLOOKUP Table Array Template

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.

VLOOKUP Table array 1

And with the help of this data, we need to map and create another table in the respective column in the below table.

VLOOKUP Table array 2

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.

Insert Function

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.

VLOOKUP Table array 4

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.

VLOOKUP Table array 5

Now drag the applied Vlookup formula to the below cells as shown below.

vlookup array table 1

Do the same procedure for the Product class as shown below.

vlookup array table 7

Drag the same formula in cell C2 to cell 9.

vlookup array excel 2

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.

vlookup array excel 10

Once done, drag the applied formula to the respective cells below.

vlookup array table 8

vlookup array excel 3

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.

vlookup array table 9

vlookup array excel 4

Example #2 – VLOOKUP Table Array

For this, we will consider the same data we saw in example 1.

vlookup table aaray example 2-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.

vlookup 7

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.

vlookup 6

Now select Owner Name as Lookup_Value as E3, our product number column.

vlookup 5

Table_Array as complete Table_1 data from cell A3 to C9.

vlookup 4

Col_Index_Num as 2, which is Product in Table_1.

vlookup 3

Range_Lookup as “0” for the exact value.

vlookup 1

Press Enter key to exit from the syntax.

vlookup 2

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.

vlookup 8

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.

vlookup 9

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.

vlookup 10

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 –

  1. IF VLOOKUP Formula in Excel
  2. VLOOKUP function in Excel
  3. VBA VLOOKUP Function
  4. Vlookup vs Index Match

Primary Sidebar

Footer

Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

© 2025 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

Download VLOOKUP Table Array Template

EDUCBA Login

Forgot Password?

EDUCBA

Download VLOOKUP Table Array Template

EDUCBA

डाउनलोड VLOOKUP Table Array Template

🚀 Limited Time Offer! - 🎁 ENROLL NOW