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]

First of all, thank you for the amazing coding.

I have implemented your code into worksheet and tried with basic table like:
1 a
1 b
3 c
5 d
3 e
4 f
etc.

It worked perfectly. However, when I used on my initial intended table, it keeps giving me "VALUE" error, my references are matching. Their, format is same "general". I am trying to concatenate comments.

So, is there any suggestion?

Show us some of the text values that is not working on so we can see them also.

 

[serquan]

Show us some of the text values that is not working on so we can see them also.

Sure, please see below:

!!!KP0016
ST01/04_M
W06/02_M
D_05.EGF.03_M/5000
E.01.AGD.04_M/251-300kVA

 

[serquan]

I think I have found when the error "#VALUE" occurs.

When the range exceeds 263 rows, function returns this error.

for example: =LookUpConcat($B17;$GM$5:$GM$267;$GN$5:$GN$267;",")

above formula returns result, but when you change $GM$267 to $GM$268, it gives you the error.

Any ideas?

 

[cortjest]

This function works perfectly for what I am looking for. But, it seems to weigh heavy on the processors, and I have to turn off automatic calculations because each calculation takes about 20 seconds.

 

[phoenixdown]

I am wondering if I can exclude results that match certain criteria.

Example.
* Europe | Italy is included
* Europe | Germany is excluded

Essentially trying to filter out non-relevant data.

 

[Rick Rothstein]

I am wondering if I can exclude results that match certain criteria.

Example.
* Europe | Italy is included
* Europe | Germany is excluded

Essentially trying to filter out non-relevant data.

This is an old thread that you have replied to and the details of the original and follow-up questions that were asked are not as clear as they were back then. In addition, there are 20+ messages in the thread. Rather than rereading all the message to become familiar with them again, and then trying to figure out how your variation might fit in with them, I would suggest you start a new thread and explain in detail what you want to do. Along with that description, I would strongly suggest you show us a sampling of your existing data and then show us what that same data should look like after being processed.

 

[reece1984]

Hi Rick,

Thank you for the great work that you do on this forum.

Could you please describe how the LookupConcatUDF in this thread can be amended so that multiple search criteria must be met in order to provide the result, for example in the same way that sumifs and countifs work but concatenating instead of summing or counting.

I think that the entry formula would then include search string 1, search range 1, search string 2, search range 2, search string 3, search range 3.

Could you help with this? Thank you.

 

[Sgdva]

Hi Rick,

Thank you for the great work that you do on this forum.

Could you please describe how the LookupConcatUDF in this thread can be amended so that multiple search criteria must be met in order to provide the result, for example in the same way that sumifs and countifs work but concatenating instead of summing or counting.

I think that the entry formula would then include search string 1, search range 1, search string 2, search range 2, search string 3, search range 3.

Could you help with this? Thank you.

1. Rick just wrote a few messages ago that it wouldn't answer anymore regarding this formula since the thread is kind of old .
2. For your problem, UDF is not a good solution, you're trying to do a whole different thing (I tried to do something like it and for a cycle of 400 strings, it took around 15-20 mins), I could think of 2 solutions:
a) Either use countifs to know how many results you will get and then do a cycle to filter the criteria in each field as many as the count if were.
b) Use Arrays as a "matrix" style, each line would meet the criteria -just store them in the same order-, then just call the arrays as needed.
You may start a new thread thinking of a new scenario, like I said, this is not a good approach neither what formulas was intended for.
PS: I even tried to speed up this calculation by converting this formula to XLL, it was slower.

 

[DReich]

That was very helpful! Thank you so much.

I was screwing around with an array formula for quite a while, then found this and it did exactly what I needed it to do in 2 seconds. This is a super helpful tool.