LookUp Value and Concatenate All Found Results

[Rick Rothstein]

Hi Rick,

I was looking at your code and it works perfectly except I was hoping to modify it a little to help me which a problem which I am having. Here's a little information regarding my problem.

I have a workbook which gives me a list of email addresses which are in no order at all, this data is laced in Sheet in Column A . eg.

Firstname1.lastname1(a)domain.com
firstname2.lastname2(a)domain.com
firstname3.lastname1(a)domain.com
Firstname1.lastname2(a)domain.com
firstname3.lastname2(a)domain.com
firstname2.lastname1(a)domain.com

What i am trying to do is search lastname1 and it display Firstname1.lastname1(a)domain.com, firstname2.lastname1(a)domain.com, firstname3.lastname1(a)domain.com.

Is there any way to change the code to allow me to do this?

If you do not mind the concatenated result not being in sort order, then the code I posted will do what you want right now. The secret is to specify the same range for both the SearchRange and the ResultRange and to specify FALSE for the optional MatchWhole argument. I guess you would also want to specify FALSE for the optional MatchCase argument as well. Give this formula try...

=LookUpConcat(".lastname1",A1:A6,A1:A6,", ",FALSE,,FALSE)

https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
https://eileenslounge.com/viewtopic.php?p=317218#p317218
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316955#p316955
https://eileenslounge.com/viewtopic.php?p=316940#p316940
https://eileenslounge.com/viewtopic.php?p=316927#p316927
https://eileenslounge.com/viewtopic.php?p=317014#p317014
https://eileenslounge.com/viewtopic.php?p=317006#p317006
https://eileenslounge.com/viewtopic.php?p=316935#p316935
https://eileenslounge.com/viewtopic.php?p=316875#p316875
https://eileenslounge.com/viewtopic.php?p=316254#p316254
https://eileenslounge.com/viewtopic.php?p=316280#p316280
https://eileenslounge.com/viewtopic.php?p=315915#p315915
https://eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315744#p315744
https://www.eileenslounge.com/viewtopic.php?p=315512#p315512
https://eileenslounge.com/viewtopic.php?p=315680#p315680
https://eileenslounge.com/viewtopic.php?p=315743#p315743
https://www.eileenslounge.com/viewtopic.php?p=315326#p315326
https://www.eileenslounge.com/viewtopic.php?f=30&t=40752
https://eileenslounge.com/viewtopic.php?p=314950#p314950
https://www.eileenslounge.com/viewtopic.php?p=314940#p314940
https://www.eileenslounge.com/viewtopic.php?p=314926#p314926
https://www.eileenslounge.com/viewtopic.php?p=314920#p314920
https://www.eileenslounge.com/viewtopic.php?f=30&t=40533&p=314837#p314837
https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA