How to return multiple lookup values in one comma separated cell?
In Excel, we can apply the VLOOKUP function to return the first matched value from a table cells, but, sometimes, we need to extract all matching values and then separated by a specific delimiter, such as comma, dash, etc… into a single cell as following screenshot shown. How could we get and return multiple lookup values in one comma separated cell in Excel?
Normally, there is no direct way for us to extract and return the multiple matching values and separated by comma into one cell, here, you can create a User Defined Function to solve this job, please do as follows:
1. Hold down the ALT + F11 keys to open the Microsoft Visual Basic for Applications window.
2. Click Insert > Module, and paste the following code in the Module Window.
VBA code: Return multiple lookup values into one comma separated cell
Function SingleCellExtract(LookupValue As String, LookupRange As Range, ColumnNumber As Integer, Char As String) 'Updateby Extendoffice Dim I As Long Dim xRet As String For I = 1 To LookupRange.Columns(1).Cells.Count If LookupRange.Cells(I, 1) = LookupValue Then If xRet = "" Then xRet = LookupRange.Cells(I, ColumnNumber) & Char Else xRet = xRet & "" & LookupRange.Cells(I, ColumnNumber) & Char End If End If Next SingleCellExtract = Left(xRet, Len(xRet) - 1) End Function
3. Then save this code and close the Module window, go back to your worksheet, and enter this formula: =SingleCellExtract(D2,A2:B15,2,",") into a blank cell that you want to return the result. And then press Enter key to get the result, see screenshot:
Note: In the above formula:
D2: indicates the cell values that you want to look up;
A2: B15: is the data range that you want to fetch the data;
2: the number 2 is the column number that matching value is to be returned;
,: the comma is the separator that you want to separate the multiple values.
You can change them to your need.
If you have Kutools for Excel, this task will no longer be a problem. The Advanced Combine Rows utility may help you combine all relative values based on a column.
|Kutools for Excel : with more than 300 handy Excel add-ins, free to try with no limitation in 30 days.|
After installing Kutools for Excel, please do as follows:
1. Select the data range that you want to combine all matching values based on a column.
2. Click Kutools > Merge & Split > Advanced Combine Rows, see screenshot:
3. In the Combine Rows Based on Column dialog box, click the column name that you want to combine based on, and then click Primary Key button, see screenshot:
4. Then click other column name that you want to combine their matched values, and click Combine to choose one separator to separate the combined values, see screenshot:
5. Then click OK button, all the corresponding cells with the same value have been combined into one cell which are separated with the comma, see screenshots:
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!