How to VLOOKUP to Return Multiple Values in One Cell in Excel?

Almost everyone works with lots of data ranging from medium to high volume datasets, and extracting information from such a sheet can become easier, with little to advanced knowledge of excel. Excel is widely used to perform simple to complex data analysis. It is a great tool for generating meaningful & insightful reports for quick decision-making. So, out of hundreds of functions available in Excel, the chosen one for today’s article is the most common &  popular “VLOOKUP”. 

VLookup

Fetching the relevant value from a table or a range by row is made possible with VLOOKUP. For the benefit of all, let’s quickly describe VLOOKUP with the syntax given below. It returns one value from the first match in the same row. 

= VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

VLOOKUP Syntax

VLOOKUP to Return Multiple Values in One Cell in Excel

Below given screenshot shows that VLOOKUP returns only one value but, there are multiple entries for the same person. The goal here is to return all the items sold by one person in one cell. 

 

There are various options however, let’s look at 2 methods to achieve the goal:

• Method 1: Using “VLOOKUP”  with some workaround and a few more steps.
• Method 2: Use of “FILTER” and “TEXTJOIN” functions to do the job.

These features work well in office 365 excel or excel 2019 version. For earlier versions, press –“Ctrl+Shift+Enter” to get the correct array functions after the formula is entered.

How to Get Started?

Create/Get any dataset. The same datasets are used for both methods.

Method 1: VLOOKUP

From the original table, create another table having two columns “Name” and “Items Sold“. For the “Name” Column, update unique names. For the “Items Sold” Column, use the IF function along with TextJoin (to combine all the return values) and Unique (to avoid duplicate items). Once the data is updated for these two columns, it’s time for VLOOKUP. 

 

Apply TEXTJOIN function to obtain the values in the same cell,

 

Below are the Vlookup results obtained:

 

Method 2: Filter with TEXTJOIN

Step 1: Identify the column(s) which returns the values. 

Filter function takes 3 arguments and the formula is – FILTER(array, include,[if empty])

Note: The FILTER function allows to filter of a range of data based on the criteria defined.

 

Step 2: Filtering dataset with the criteria (value in cell L3 matches with names in Rep_Name Column). Formula in cell L5:

= FILTER(Table1[Item],Table1[Rep_Names]=L3,”Not Found”)

As shown below, the name “Ki” which is not in the table hence the result is “Not Found”

 

Another example, this time with the name which exists in the table.

 

As shown below, the return values spill over to other cells so, to get all the values in the same cell,  apply TEXTJOIN.

 

Step 3: Applying the TEXTJOIN function in the existing formula.  

Syntax: TEXTJOIN(delimiter,ignore_empty,text1,..)

 

Step 4: Now the required output is here.  

 

One small note, we should apply the UNIQUE function encapsulating the FILTER function to remove any duplicate items. This is not shown in the screenshot captured, however, the formula should look like this:

= UNIQUE(Filter(Table1[Item],Table1[Rep_Name]=L3,”Not Found”))

And the final step to get TextJoin:

= TEXTJOIN(” ,”,TRUE,UNIQUE(FILTER(Table1[Item],Table1[Rep_Name]=L3,”Not Found”)))

Conclusion

There are several ways to achieve the same result using different functions. Using VLOOKUP was good, but it involved additional steps to get the final output. However, the FILTER function was straightforward and easy to follow.