EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel VLOOKUP Table Array
Secondary Sidebar
Excel Functions
  • Lookup Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • 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 (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

VLOOKUP Table Array

By Ashwani JaiswalAshwani Jaiswal

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.

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.

Start Your Free Excel Course

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

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.

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,452 ratings)

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.

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 like 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, 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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA
Free Excel Course

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

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

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

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

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

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download VLOOKUP Table Array Template

EDUCBA

Download VLOOKUP Table Array Template

EDUCBA

डाउनलोड VLOOKUP Table Array Template

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more