if vlookup is match then concatenate

Hi Folks,

I'm having some difficulty is pulling a formula together...

On sheet 1 I would like to return a concatenation of text cells (from worksheet 2) if the there is a match in the vlookup...

Sheet 1 is the master data sheet with unique ID's, worksheet 2 is a data dump where there could be many entries for one Unique ID and I'd like to concatenate the details of each entry in one cell on worksheet 1.

Result required: worksheet 1: A1 = CP1234  B1 = Text 1, statement 1, vague 1.

I was thinking of a nested formula with the following statements: IF, VLOOKUP, CONCATENATE.

Sheet 1

A1 = CP1234

A2 = CP1235

A3 = CP1236

 

Sheet 2

A1 = CP1234    B1= Text 1

A2 = CP1235    B2= sample1

A3 = CP1236    B3= word1

A4 = CP1234    B4= statement 1

A5 = CP1235    B5= happy1

A6 = CP1236    B6= days1

A7 = CP1234    B7= vague 1

A8 = CP1235    B8= ideas 1

A9 = CP1236    B8= etc 1

 

Your help is greatly appreciated...

Kind Regards

Tracey

 

One way...

If you can download and install the free add-in Morefunc.xll from:

http://www.download.com/Morefunc/3000-2077_4-10423159.html

Then, you can use this array formula** entered on Sheet1 in cell B1:

=SUBSTITUTE(MCONCAT(IF(Sheet2!A1:A9=A1,", "&Sheet2!B1:B9,"")),", ","",1)

** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT key then hit ENTER.

Copy down as needed

Note that the concatenated string is limited to no more than 255 characters.

--
Biff
Microsoft Excel MVP

Hi,

One way with a user defined function. ALT+F11 to open VB editor, right click 'ThisWorkbook' and insert module and paste the code below in and then close VB editor.

Back on the worksheet put this in b1 and drag down.

=MultiLookup(A1,Sheet2!$A$1:$A$9)

Function MultiLookup(val As Range, rng As Range) As String
Dim c As Range
For Each c In rng
    If c.Value = val.Value Then
        MultiLookup = MultiLookup + c.Offset(, 1) + ", "
        End If
Next
If MultiLookup = "" Then
    MultiLookup = "No Match"
Else
MultiLookup = Left(MultiLookup, Len(MultiLookup) - 2)
End If
End Function