VLOOKUP to Return Multiple Values (Table of Contents)
VLOOKUP to Return Multiple Values
We all know that Vlookup in excel is used to lookup 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 values in a table which are duplicate then we would only get the first value from the lookup range. The below the value which are duplicate will not reflect or lookup any value from the table range. But this is possible. We will be seeing, how to lookup multiple values in the below examples.
How to Use VLOOKUP to Return Multiple Values?
We cannot directly use lookup when we want 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. 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. Below we have column A with some names and all are duplicate.
To make these names unique value, we can add any number or special character so that all values will become unique. As we can see, in column B, after added numbers to each cell value, all the values have become unique.
Examples of VLOOKUP to Return Multiple Values
Lets us discuss the examples of VLOOKUP to Return Multiple Values.
Example #1
In this, we will be seeing, how to use Vlookup to get multiple values from one table to another. We have two tables below. Each table has same headers and also has the same Owner names 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 getting repeated.
To avoid such situations, we will insert a column in the first table and make a key using the Owner name and Product columns. We can use any type of separator here. In the Key column, we are using an underscore as a separator.
We will be able to see the final unique key column A as shown below.
Now in table 2, we will be applying Vlookup to get the value from Table 1 to Table 2. Insert vlookup function as shown below.
As per syntax, we need to select the lookup value in the same way we have created in table 1. For that, combine or concatenate F1 and G1 value with the help of underscore.
In the table array, select the complete table 1.
As we want to get Quantity Sold numbers from Table1 to Table2, so we will be selecting column 4 as Col Index.
Once we press enter and drag the formula till the end, all the values from the table1 will be fetched to table2’s Quantity Sold column.
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 will better trim that table and work on its small part.
To lookup multiple values, here we will be using the Index function. The index function in excel is used to lookup the value in the form of a matrix. This means, Index lookups the value in the whole table with the help of chosen reference Column and Row index number.
As per the syntax of Index function, we need to select the array from where we want to get the value. Here our array is Column C.
Now as per syntax we will be using ROW and COLUMN numbers to get values. Here, we are using the Small function to get the smallest value first from the lookup array.
If we press enter we will be getting the value for the first cell only. To execute this function properly, press SHIFT + CTRL + Enter together. Then only after dragging we will be getting the looked values from table1.
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 value against the same lookup value.
- There is no limit to the values which we want to look up using multiple value criteria.
Things to Remember
- While using the method shown in example-2, always remember to press SHIFT + CTRL + ENTER keys together to execute the applied formula. If we just directly press enter, then 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 of the duplicate values and lookup the value in the same order.
- We can use BLANK cell as well as a separator.
- There are N number of ways to create a key that is not limited to the steps and process shown in the above examples.
- Even if we add a SPACE, it will be helpful for creating 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 –
