WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Blog
  • Free Video Tutorials
  • Courses
  • All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Vlookup to Return Multiple Values

Vlookup to Return Multiple Values

By Jeevan A YJeevan A Y | Reviewed By Dheeraj VaidyaDheeraj Vaidya, CFA, FRM

Excel Vlookup to Return Multiple Values

One of the key functionality of the VLOOKUP function is that it will work for unique values, and if there are any duplicate values, then whatever first found value will be returned for all the other lookup values as well. This is one of the key things we need to keep in mind while applying a VLOOKUP formula. When the lookup value appears multiple times, and if it has multiple values, then we need to include different strategies. In this article, we will show you how to return multiple values using the VLOOKUP function.

How to Return Multiple Values using Vlookup Function?

As we have told above, VLOOKUP works for unique values, and for duplicate values, it will return the first found value.

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

For example, look at the below data.

VLOOKUP to Return Multiple Values 1

We have “Table 1” & “Table 2” in “Table 1” we have fruits and their prices in different cities, and for “Table 2,” we need to arrive at cost price from “Table 1” using the VLOOKUP function. Apply the VLOOKUP function first.

VLOOKUP to Return Multiple Values 1-1

  • Look, we have got the same price for all the cities. For example, in “Table 1” for “Apple” in the city “Bangalore,” we have 108 since this is the first value found in the table for “Apple” it has returned the same for all the cities.
  • Similarly, for “Grapes” to the first value is 79, and the same has been returned for all the cities, and in the case of “Orange” as well, it returned 56 for all the cities.

So, in these cases, we need to create a helper column to create a unique lookup value list. Each fruit has different prices for each city, so a combination of fruit name and city can create a unique list, insert a helper column, and combine fruit name and city name.

VLOOKUP to Return Multiple Values 1-2

So each fruit name combined with the city by including backward slash (/) as the separator between fruit name and city name.

Now come back to “Table 2” and open the VLOOKUP function.

VLOOKUP to Return Multiple Values 1-3

Now we need to include the same strategy of the helper column here to choose lookup value, first choose fruit name.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

VLOOKUP to Return Multiple Values 1-4

Then combine backward slash before combining with the city name.

VLOOKUP to Return Multiple Values 1-5

Now combine the city name.

VLOOKUP to Return Multiple Values 1-6

Now the lookup value is similar to the helper column. Now choose the table array starting from the helper column.

VLOOKUP to Return Multiple Values 1-7

Now mention the column number as 4 and range lookup as FALSE or 0.

VLOOKUP to Return Multiple Values 1-8

There you go, we have a new cost price list with accurate numbers, say thanks to the helper column, or the combination of Fruit Name & City.

Use Alternative Methods for Multiple Values

We have seen how the helper column can be helpful in fetching the multiple values using the VLOOKUP formula. But imagine the situation below.

Example 2

In this, we don’t have any city name to create a concatenation column, so we may need to employ different strategies. Below is the complex formula that we can use to get the multiple values of duplicate unique values.

=INDEX($B$2:$B$11, SMALL(IF( E3=$A$2:$A$11, ROW($A$2:$A$11)- ROW($A$2)+1), ROW(1:1)))

Example 2-1

Note: The above formula is an array formula so need to close with Ctrl + Shift + Enter.

This formula looks lengthy, isn’t it? However, we have another alternative method, i.e., combine the fruit name with their count on the list.

Apply the below COUNTIF function to create a helper column.

Example 2-2

The above function will give us the count of each fruit combined with the fruit name itself. For an example, look at the row number 4 in this, we have a count of “Apple” 2 times, and so count says 2 and combined with the fruit name gives us “2Apple”. So this will create a unique list of fruits.

Now create a lookup table like the below one.

Example 2-2

Now open the VLOOKUP function in the lookup table, i.e., in H3 cell.

Example 2-3

In the helper column, the first value combined counts, so here to select the numerical value and then combine with the fruit name.

Example 2-4

Now select the table and enter the column index number to get the result.

Example 2-5

Things to Remember

  • VLOOKUP returns the same value for the lookup values if the lookup value has duplicate names.
  • To fetch multiple values of the same lookup value, we need to create helper columns by using any of the above 3 methods.

Recommended Articles

This has been a guide to VLOOKUP to Return Multiple Values. Here we discuss how to return multiple values using the VLOOKUP function and to use alternative methods for multiple values and downloadable excel template. You may also look at these useful functions in excel –

  • Use VLOOKUP with Power BI
  • Apply VLOOKUP for Text
  • Apply VLOOKUP with Match
  • VLOOKUP Excel Tutorial
10 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Vlookup to Return Multiple Values Excel Template

Special Offer - All in One Financial Analyst Bundle (250+ Courses, 40+ Projects) View More