Updated August 10, 2023
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.
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.
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.
Examples of VLOOKUP to Return Multiple Values
Lets us discuss the examples of VLOOKUP to Return Multiple Values.
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.
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.
We can see the final unique key column A, as shown below.
In Table 2, we will apply Vlookup to get the value from Table 1 to Table 2. Insert the vlookup function as shown below.
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.
In the table array, select the complete table 1.
As we want to get Quantity Sold numbers from Table1 to Table2, we will select 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 the 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 we will better trim that table and work on its small part.
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.
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.
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.
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 –
