Vlookup multiple Values then Concatenate all values in One cell

[Wsr712]

Hi Everyone,

Excel Noob here, Here's my situation, most of the time I organize countries
by their region to cut time, i hoping that Excel Vlookup would help me out, but as other noob like me experience is that it only throws the first value it gets and disregards the rest. For instance, Asia Pacific, there are several countries under that region like
Malaysia, Vietnam, Cambodia etc. so my given would be as below

Asia Pacific Malaysia,
Asia Pacific China
Asia Pacific Philippines

Europe Amsterdam
Europe Italy
Europe France

The output I would like would be:

Asia Pacific: Malaysia,, China, Philippines
Europe: Amsterdam, Italy, France


This would save me time Appreciate the help and Thanks in advance!

 

[Rick Rothstein]

You can use my LookUpConcat UDF (user defined function) found in my min-blog article here...

LookUp Value and Concatenate All Found Results

Once you have installed the UDF, your formula on the worksheet would look something like this...

=LookUpConcat("Asia Pacific",A1:A7,B1:B7,", ")

where, of course, you would change the ranges to match your actual setup.


HOW TO INSTALL UDFs
------------------------------------
If you are new to UDFs, they are easy to install and use. To install it, simply press ALT+F11 to go into the VB editor and, once there, click Insert/Module on its menu bar, then copy/paste the above code into the code window that just opened up. That's it.... you are done. You can now use LookUpConcat just like it was a built-in Excel function (as shown above).

 

[Wsr712]

Hi Rick,

thanks for your help this was indeed helpful to my team.

 

[lainey76]

Hi Rick,
This worked great on my first columns but when I tried to apply it to my second value to return different values I get a #value! error? It is not a format issue as I tried the formula from the first column in the same cell and it worked fine.
Here is the formula from column Z : LookUpConcat(AB54,'Battery & associated systems'!$V$2:$V$20000,'Battery & associated systems'!$M$2:$M$20000,"/")
And here is the one from column AA: LookUpConcat(AC54,'Power & associated systems'!$AJ$2:$AJ$20000,'Power & associated systems'!$Q$2:$Q$20000,"/")
They reference different sheets but that are built the same way. Would you by any chance have an idea of what the issue might be?
Thanks

 

[Rick Rothstein]

Hi Rick,
This worked great on my first columns but when I tried to apply it to my second value to return different values I get a #value! error? It is not a format issue as I tried the formula from the first column in the same cell and it worked fine.
Here is the formula from column Z : LookUpConcat(AB54,'Battery & associated systems'!$V$2:$V$20000,'Battery & associated systems'!$M$2:$M$20000,"/")
And here is the one from column AA: LookUpConcat(AC54,'Power & associated systems'!$AJ$2:$AJ$20000,'Power & associated systems'!$Q$2:$Q$20000,"/")
They reference different sheets but that are built the same way. Would you by any chance have an idea of what the issue might be?
Thanks

Rather than trying to guess, why don't you attach a copy of your workbook to your reply so we can look at and test against your data?

 

[Rick Rothstein]

Rather than trying to guess, why don't you attach a copy of your workbook to your reply so we can look at and test against your data?

Sorry, I forgot which forum I was on... there is no attaching files here. Why don't you send your workbook directly to me so I can try to see what the problem is. My email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbol they spell out). Also remind me of thread's title in your email so I can re-find this thread easier.

 

[Rick Rothstein]

Hi Rick,
This worked great on my first columns but when I tried to apply it to my second value to return different values I get a #value! error? It is not a format issue as I tried the formula from the first column in the same cell and it worked fine.
Here is the formula from column Z : LookUpConcat(AB54,'Battery & associated systems'!$V$2:$V$20000,'Battery & associated systems'!$M$2:$M$20000,"/")
And here is the one from column AA: LookUpConcat(AC54,'Power & associated systems'!$AJ$2:$AJ$20000,'Power & associated systems'!$Q$2:$Q$20000,"/")
They reference different sheets but that are built the same way. Would you by any chance have an idea of what the issue might be?
Thanks

Okay, I discovered the problem... some of your concatenations in Column AJ are returning #N/A errors and that is affecting my code. Here is revised code to work around that problem (actually, the code is the same except for the line I highlighted in red)...

Function LookUpConcat(ByVal SearchString As String, SearchRange As Range, ReturnRange As Range, _
                      Optional Delimiter As String = " ", Optional MatchWhole As Boolean = True, _
                      Optional UniqueOnly As Boolean = False, Optional MatchCase As Boolean = False)
                   
  Dim X As Long, CellVal As String, ReturnVal As String, Result As String
  
  If (SearchRange.Rows.Count > 1 And SearchRange.Columns.Count > 1) Or _
     (ReturnRange.Rows.Count > 1 And ReturnRange.Columns.Count > 1) Then
    LookUpConcat = CVErr(xlErrRef)
  Else
    If Not MatchCase Then SearchString = UCase(SearchString)
    For X = 1 To SearchRange.Count
      If IsError(SearchRange(X)) Then GoTo Continue
      If MatchCase Then
        CellVal = SearchRange(X).Value
      Else
        CellVal = UCase(SearchRange(X).Value)
      End If
      ReturnVal = ReturnRange(X).Value
      If MatchWhole And CellVal = SearchString Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      ElseIf Not MatchWhole And CellVal Like "*" & SearchString & "*" Then
        If UniqueOnly And InStr(Result & Delimiter, Delimiter & ReturnVal & Delimiter) > 0 Then GoTo Continue
        Result = Result & Delimiter & ReturnVal
      End If
Continue:
    Next
    
    LookUpConcat = Mid(Result, Len(Delimiter) + 1)
  End If
End Function

I do note, however, that some of the cells that match up in Column AJ have nothing in the return cell in Column Q... because of this, you are going to see one or more trailing backslashes (the empty string for a matching item is a value that will be returned).

 

[lainey76]

Sorry, I forgot which forum I was on... there is no attaching files here. Why don't you send your workbook directly to me so I can try to see what the problem is. My email address is rickDOTnewsATverizonDOTnet (just replace the upper case letters with the symbol they spell out). Also remind me of thread's title in your email so I can re-find this thread easier.

Wow! Thank you so much! It worked like a charm! )And it's fine for the trailing backslashes since I use this as a guideline and it prevents me from opening up another program for verifications.
Thanks again for all your help!

 

[Danuta123]

Dear Rick,

Absolutely marvellous! I was stuck and then..........Badah! Mr Rothstein's really rocking code, problem solved.

Thanks a million, you've made my day!

 

[wadings]

Hi Rick,

I wonder if you could help me out.

I have this scenario, I have various countries and their area codes that i would like to combine each state and its area codes in one cell using Vlookup. But when I do that Vlookup returns only first answer. I tried to use the above scribt but i get only error since instead of the Text i put in the Cell.
This is my scenario.
From this format,

[TABLE="width: 433"]
<colgroup><col><col><col></colgroup><tbody>[TR]
[TD]Algeria - Algiers
[/TD]
[TD][/TD]
[TD="align: right"]21320[/TD]
[/TR]
[TR]
[TD]Algeria - Algiers
[/TD]
[TD="align: right"]21321
[/TD]
[/TR]
[TR]
[TD]Algeria - Algiers
[/TD]
[TD][/TD]
[TD="align: right"]21322


[/TD]
[/TR]
[TR]
[TD]
To this Format;
[TABLE="width: 433"]
<tbody>[TR]
[TD]Algeria - Algiers
[/TD]
[TD][/TD]
[TD="align: right"]21320, 21321,21322
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]
Main issue is I have an excel table with more than 5000 rows and cannot type each text as required by LookupConcat.

Can you help me out?

Regards
wadings