# Lookup Value and Combine Texts

#### contraububu

##### New Member
 A B Dan Brown Banana Jeff Hill Grapes Beth Sims Berries Dan Brown Lemons Beth Sims Beets Jeff Hill Lettuce Beth Sims Carrots Jeff Hill Chocolate Dan Brown Apples

<tbody>
</tbody>
From the given example, I would like to lookup for customer "Dan Brown" and return the list of items he purchased "Banana, Lemons, Apples" in one cell. Thank you very much for helping me with the formula!

### Excel Facts

What is the fastest way to copy a formula?
If A2:A50000 contain data. Enter a formula in B2. Select B2. Double-click the Fill Handle and Excel will shoot the formula down to B50000.

##### MrExcel MVP
Add the following function code for ACONCAT to your workbook using Alt+F11...

Function aconcat(a As Variant, Optional sep As String = "") As String
' Harlan Grove, Mar 2002
Dim y As Variant
If TypeOf a Is Range Then
For Each y In a.Cells
aconcat = aconcat & y.Value & sep
Next y
ElseIf IsArray(a) Then
For Each y In a
aconcat = aconcat & y & sep
Next y
Else
aconcat = aconcat & a & sep
End If
aconcat = Left(aconcat, Len(aconcat) - Len(sep))
End Function

Let Sheet1, A2:B10, house the data sample.

E1 houses a name like **** Brown.

E2, control+shift+enter, not just enter:
Rich (BB code):
``````=REPLACE(ACONCAT(IF(\$A\$2:\$A\$10=\$E1,", "&\$B\$2:\$B\$10,"")),1,2,"")
``````

#### Carguy37122

##### New Member
where in the code is this added?

=REPLACE(ACONCAT(IF(\$A\$2:\$A\$10=\$E1,", "&\$B\$2:\$B\$10,"")),1,2,"")

Thanks!

#### contraububu

##### New Member
Aladin Akyurek...is there a more simpler approach as I don't know anything about setting up VBA?

##### MrExcel MVP
where in the code is this added?

=REPLACE(ACONCAT(IF(\$A\$2:\$A\$10=\$E1,", "&\$B\$2:\$B\$10,"")),1,2,"")

Thanks!

E1: Dan Brown
E2: Control+shift+enter, not just enter:

=REPLACE(ACONCAT(IF(\$A\$2:\$A\$10=\$E1,", "&\$B\$2:\$B\$10,"")),1,2,"")

Aladin Akyurek...is there a more simpler approach as I don't know anything about setting up VBA?

You wanted the results, tucked in a single cell. Hence the approach.

Here is the workbook that implements the set up:
https://dl.dropboxusercontent.com/u/65698317/contraububu sublist with aconcat.xlsm

Replies
2
Views
778
Replies
6
Views
675
Replies
7
Views
1K
Replies
0
Views
463
Replies
4
Views
2K

1,191,191
Messages
5,985,216
Members
439,948
Latest member
Xearo96

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back