EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login

VLOOKUP Table Array

By Ashwani JaiswalAshwani Jaiswal

Home » Excel » Blog » Lookup & Reference Functions in Excel » VLOOKUP Table Array

VLOOKUP Table Array

VLOOKUP Table Array (Table of Contents)

  • Introduction to VLOOKUP Table Array
  • How to Use VLOOKUP Table Array in Excel?

Introduction to VLOOKUP Table Array

Vlookup Table Array is used for finding and looking up the required values in the form of a 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.

Start Your Free Excel Course

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

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.

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

VLOOKUP Table array 1

And with the help of this data, we need to map and create another table in the respective column in 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

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 the 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 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 below respective cells as shown below.

Popular Course in this category
Excel Training (18 Courses, 9+ Projects)18 Online Courses | 9 Hands-on Projects | 95+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.9 (7,056 ratings)
Course Price

View Course

Related Courses
Excel Advanced Training (14 Courses, 23+ Projects)

vlookup array table 8

vlookup array excel 3

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 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 which we have seen in example 1.

vlookup table aaray example 2-1

We can name the table as Table 1 as per heading and the 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.

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 which is 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 the value of 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 that it doesn’t have any value related to product number 881 in Table 1.

vlookup 10

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 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 with each other 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 along with practical examples and a downloadable excel template. You can also go through our other suggested articles –

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

All in One Excel VBA Bundle (120+ Courses, 30+ Projects)

120+ Online Courses

30+ Projects

500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP True
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (110+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (13+)
  • Maths Function in Excel (46+)
  • TEXT and String Functions in Excel (25+)
  • Date and Time Function in Excel (22+)
  • Statistical Functions in Excel (58+)
  • Information Functions in Excel (4+)
  • Excel Charts (55+)
  • Excel Tips (220+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (20+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

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

EDUCBA Login

Forgot Password?

EDUCBA
Free Excel Course

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

*Please provide your correct email id. Login details for this Free course will be emailed to you
Book Your One Instructor : One Learner Free Class

Let’s Get Started

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

EDUCBA

Download VLOOKUP Table Array Template

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

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

Special Offer - Online EXCEL COURSE Learn More