- To post as a guest, your comment is unpublished.· 2 years agoHello, sym-john,
Maybe the below article can solve your problem, please view it:
How to vlookup and concatenate multiple corresponding values in Excel?
As we all known, the Vlookup function in Excel can help us to lookup a value and return the corresponding data in another column, but in general, it can only get the first relative value if there are multiple matching data. In this article, I will talk about how to vlookup and concatenate multiple corresponding values in only one cell or a vertical list.
Supposing, I have the following range of data, to get all the corresponding values based on a specific value vertically as following screenshot shown, you can apply an array formula.
1. Enter this formula: =IF(COUNTIF($A$1:$A$16,$D$2)>=ROWS($1:1),INDEX($B$1:$B$16,SMALL(IF($A$1:$A$16=$D$2,ROW($1:$16)),ROW(1:1))),"") into a blank cell where you want to put the result, for example, E2, and then press Ctrl + Shift + Enter keys together to get the relative value base on a specific criterion, see screenshot:
Note: In the above formula:
A1:A16 is the column range which contains the specific value you want to look for;
D2 indicates the specific value you want to vlookup;
B1:B16 is the column range that you want to return the corresponding data from;
$1:$16 indicates the rows reference within the range.
2. Then select cell E2, and drag the fill handle down to the cells until you get blank cells, and all matching values are listed into the column as following screenshot shown:
Instead of getting the relative values vertically, sometimes, you want to the matching values into one cell and concatenate them with a specific separator. In this case, the following User Defined Function may do you a favor.
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: Vlookup and concatenate multiple matching values in a cell
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long) 'updateby Extendoffice Dim x As Range Dim result As String result = "" For Each x In lookuprange If x = lookupval Then result = result & " " & x.Offset(0, indexcol - 1) End If Next x CusVlookup = result End Function
3. Then save and close this code, go back to the worksheet, and enter this formula: =cusvlookup(D2,A1:B16,2) into a blank cell where you want to put the result, and press Enter key, all corresponding values based on a specific data have been returned into one cell with space separator, see screenshot:
Note: In the above formula: D2 indicates the cell values that you want to look up, A1: B16 is the data range that you want to fetch the data, the number 2 is the column number that the matching value is to be returned from, you can change theses references to your need.
If you have Kutools for Excel, with its Advanced Combine Rows feature, you can quickly finish this job with ease. This feature can help you to combine all matching values with a specific delimiters based on the same data in another 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 get the corresponding values based on the specific data.
2. Then click Kutools > Merge & Split > Advanced Combine Rows, see screenshot:
3. In the Advanced Combine Rows dialog box, click the column name that you want to combine based on, then click Primary Key button, see screenshot:
4. Then click another column name that you want to return the matched values, and click Combine to choose one separator to separate the combined values, see screenshot:
5. And then click Ok button, all the corresponding values based on same values have been combined together with a specific separator, see screenshots:
You are guest
or post as a guest, but your post won't be published automatically.
- To post as a guest, your comment is unpublished.· 2 years agoIs there any way to get the unique "name" for "class1"
- To post as a guest, your comment is unpublished.· 2 years agoThis is working great for me - is there anyway to change it that it checks if the cell contains rather than a complete match? Basically I have a list of tasks where:
Column A: Dependencies (eg 10003 10004 10008)
Column B: Task Reference (eg 10001)
Column C: Dependent Tasks (the column for the formula result) - where it would lookup the task reference to see which rows contain it in Column A, and then list the Task Reference of those tasks.
Row | Column A | Column B | Column C
1 | | 10001 | 10002 10003
2 | 10001 | 10002 | 10003
3 | 10001 10002 | 10003 |
- To post as a guest, your comment is unpublished.· 2 years agoyou would want to use the Instr() function which will check for something in a string of text in a cell. You can also use Left() and Right() if you are looking for the starting or ending details.
- To post as a guest, your comment is unpublished.· 2 years agoThe cusVlookup worked great for me. Another way to have a different separator is to wrap in two substitute functions. The first (from inside to out) replaces the first space with no space, the second replaces all other spaces with a " / " in mine. Could use "," if you want commas.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,2)," ","",1)," "," / ")
Also, if your lookup value isn't the first column, you can use 0 or negative numbers to go to column to the left.
=SUBSTITUTE(SUBSTITUTE(cusVlookup(D2,Table1,-1)," ","",1)," "," / ")
- To post as a guest, your comment is unpublished.· 2 years agoHi, jeff,
Thanks for your sharing, you must be a warmhearted man.
- To post as a guest, your comment is unpublished.· 3 years agoI have to say, I have been trying to get a formula for combining multiple values and returning them to a single cell for 2 days now. This "How To" has saved me!! Thank you SO much! I would never have gotten it without your Module!
I do have 2 questions though. I have the deliminator as a comma instead of a space and because of that it starts out with a comma. Is there a way to prevent the start comma but keep the rest?
My second question is; When I use the fill handle it changes the range values as well as the cell value I want to look up. I want it to continue to change the cell number I want to look up but keep the same range values. How can I make this happen?
Thank you so much for your help!!
- To post as a guest, your comment is unpublished.· 3 years agoIs there a way to delete the duplicate values in the concatenate?
- To post as a guest, your comment is unpublished.· 3 years agoHello, Jacob,
May be the following article can help you to solve your problem.
Please try, hope it can help you!
- To post as a guest, your comment is unpublished.· 2 years agoIs there a way to list the duplicate values only once, using the vba code and formula above? I am not sure where to put the countif>1 statement in the formula bar, or in the vba itself. Please help
- To post as a guest, your comment is unpublished.· 11 months agoyou can add two extra condition to skip blank cells and to skip duplicates:For i = 1 To CriteriaRange.Count
If CriteriaRange.Cells(i).Value = Condition Then
If ConcatenateRange.Cells(i).Value <> "" Then 'SKIP BANKS
If InStr(xResult, ConcatenateRange.Cells(i).Value) = 0 Then 'SKIP IF FOUND DUPLICATE
xResult = xResult & Separator & ConcatenateRange.Cells(i).Value
- To post as a guest, your comment is unpublished.· 3 years agoThis is amazing but i am looking for something else, i have a table with RollNo StudentName sub1, sub2, sub3 ... Total Result, When I enter Rollnumber it should give a result like "SName Sub1 64, sub2 78,... Total 389, Result pass", is it possible
- To post as a guest, your comment is unpublished.· 4 years agoLoved the function for Excel 2013 but amended it slightly to change the separating character to ";" instead of " " and then remove the prefixed ";" from the concantenated values
Results matching values in my example would have ;result01 or ;result01;result02 . Added the extra If Left(xResult, 1) = ";" to remove any extra ";" at the beginning of the string if it is the 1st character. I'm sure there is a neater way of doing it but it worked for me. :)
Function CusVlookup(pValue As String, pWorkRng As Range, pIndex As Long)
Dim rng As Range
Dim xResult As String
xResult = ""
For Each rng In pWorkRng
If rng = pValue Then
xResult = xResult & ";" & rng.Offset(0, pIndex - 1)
If Left(xResult, 1) = ";" Then
xResult = MID(xResult,2,255)
CusVlookup = xResult
- To post as a guest, your comment is unpublished.· 4 years agoMake if condition for result if empty.
Function CusVlookup(lookupval, lookuprange As Range, indexcol As Long)
'updateby Extendoffice 20151118
Dim x As Range
Dim result As String
result = ""
For Each x In lookuprange
If x = lookupval Then
If Not result = "" Then
result = result & " " & x.Offset(0, indexcol - 1)
result = x.Offset(0, indexcol - 1)
CusVlookup = result
- To post as a guest, your comment is unpublished.· 5 years agoWhen using the cusvlookup is there a way to add the last name as well with a comma in between that might appear in Column C
- To post as a guest, your comment is unpublished.· 5 years agoHow to get the result. Please help.
data data1 result
a 1 a1
b 2 a2