EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Functions VLOOKUP to Return Multiple Values

VLOOKUP to Return Multiple Values

Ashwani Jaiswal
Article byAshwani Jaiswal
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated August 10, 2023

VLOOKUP to Return Multiple Values main

VLOOKUP to Return Multiple Values

We all know that Vlookup in Excel is used to look up the exact or approximate match, and we have all been doing this on our regular tasks. Vlookup looks up the value from the selected table range and returns the exact match as per the cell value it maps. But when we have multiple duplicate values in a table, we would only get the first value from the lookup range. The duplicate values below will not reflect or look up any value from the table range. But this is possible. The examples below show us how to look up multiple values using VLOOKUP to Return Multiple Values.

Start Your Free Excel Course

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

How to Use VLOOKUP to Return Multiple Values?

We can create unique value by combining different cells, or we can use different types of separators such as / (Slash), – (Hyphen), _ (underscore), or even spaces. We cannot directly use lookup to get multiple values in return. For this, we need to find the provision so that we would have unique values among all the values. Below we have column A with some names, and all are duplicates.

VLOOKUP to Return Multiple Values 1-1

To make these names unique values, we can add any number or special character so that all values will become unique. As we can see, in column B, all the values have become unique after adding numbers to each cell value.

VLOOKUP to Return Multiple Values 1-2

Examples of VLOOKUP to Return Multiple Values

Lets us discuss the examples of VLOOKUP to Return Multiple Values.

You can download this VLOOKUP to Return Multiple Values Excel Template here – VLOOKUP to Return Multiple Values Excel Template

Example #1

In this, we will see how to use Vlookup to get multiple values from one table to another. We have two tables below. Each table has the same headers and Owner and Product names in the same sequence. Now if we apply the vlookup in cell G2 to get the quantity sold for each Owner name, then we will only get the first value of each owner name as the owner names are repeated.

VLOOKUP to Return Multiple Values 1-3

We will insert a column in the first table and make a key using the Owner name and Product columns to avoid such situations. In the Key column, we are using an underscore as a separator. We can use any separator here.

VLOOKUP to Return Multiple Values 1-4

VLOOKUP to Return Multiple Values 1-5

We can see the final unique key column A, as shown below.

VLOOKUP to Return Multiple Values 1-6

In Table 2, we will apply Vlookup to get the value from Table 1 to Table 2. Insert the vlookup function as shown below.

VLOOKUP to Return Multiple Values 1-7

As per syntax, we need to select the lookup value as we have created in Table 1. For that, combine or concatenate F1 and G1 values with the help of an underscore.

Quanity Sold

In the table array, select the complete table 1.

VLOOKUP to Return Multiple Values 1-8

As we want to get Quantity Sold numbers from Table1 to Table2, we will select column 4 as Col Index.

Quanity Sold 1

Once we press enter and drag the formula till the end, all the values from the table1 will be fetched to the table2’s Quantity Sold column.

Quanity Sold 2

Example #2

There is another way to apply Vlookup to get multiple values in return. For this, we will be using the same table which we have seen in the above example, but we will better trim that table and work on its small part.

Quanity Sold 3

To look up multiple values, we will use the Index function here. The index function in Excel is used to look up the value as a matrix. This means Index lookups at the value in the table with the help of chosen reference Columns and Row index numbers.

As per the syntax of the Index function, we need to select the array from where we want to get the value. Here our array is Column C.

Index Vlookup

As per syntax, we will use ROW and COLUMN numbers to get values. Here, we use the Small function to get the smallest value first from the lookup array.

Index Vlookup 1

If we press enter, we will get the value for the first cell only. To execute this function properly, press SHIFT + CTRL + Enter together. Then only after dragging will we be getting the looked values from table1.

Index Vlookup 2

Pros of Vlookup to Return Multiple Value

  • It is quite helpful in mapping or looking up the values against duplicate values.
  • We can see a single or all the values against the same lookup value.
  • There is no limit to the values we want to look up using multiple value criteria.

Things to Remember

  • While using the method shown in example-2, always press the SHIFT + CTRL + ENTER keys together to execute the applied formula. If we directly press enter, we would get the value only for the first cell, not for every down cell.
  • Instead of concatenating different cell values, we give numbers to each duplicate value and look up the value in the same order.
  • We can use a BLANK cell as well as a separator.
  • Numerous ways exist to create a wider key than the steps and process shown in the above examples.
  • Even if we add a SPACE, it will help create a unique key for Vlookup to return multiple values.

Recommended Articles

This has been a guide to VLOOKUP to Return Multiple Values. Here we discuss How to Use VLOOKUP to Return Multiple Values, along with practical examples and a downloadable Excel template. You can also go through our other suggested articles –

  1. VLOOKUP Names
  2. VLOOKUP Table Array
  3. VLOOKUP For Text
  4. VLOOKUP with Sum
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

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

EDUCBA

Download VLOOKUP to Return Multiple Values Excel Template

Let’s Get Started

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

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

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

Forgot Password?

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

EDUCBA

Download VLOOKUP to Return Multiple Values Excel Template

EDUCBA

डाउनलोड VLOOKUP to Return Multiple Values Excel 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