Multiple Lookup in single cell

[drshkd]

I have a multiple lookup formula which fetches data and shows multiple datas in a single cell. All datas are separated by commas. Is there any way to show multiple datas in separate lines in a single cell. Thanks

 

[sheetspread]

You can ctrl-h replace the commas with ctrl-j or change the commas in your formula to char(10). Adjust the row width after.

 

[drshkd]

Thanks for your replay but cannot understand what you are suggesting. The formula in the cell is " =IFERROR(MultipleLookupNoRept(selectedCell,$A$2:$C$552,2)," ") " which relates to a code.

Function MultipleLookupNoRept(Lookupvalue As String, LookupRange As Range, ColumnNumber As Integer)
Dim i As Long
Dim Result As String
For i = 1 To LookupRange.Columns(1).Cells.Count
If LookupRange.Cells(i, 1) = Lookupvalue Then
For J = 1 To i - 1
If LookupRange.Cells(J, 1) = Lookupvalue Then
If LookupRange.Cells(J, ColumnNumber) = LookupRange.Cells(i, ColumnNumber) Then
GoTo Skip
End If
End If
Next J
Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & ","
Skip:
End If
Next i
MultipleLookupNoRept = Left(Result, Len(Result) - 1)
End Function


Now the result is text strings like

House Boat, Paper
Plane, Green Grass,
Red Baloon, Pink
Shoes

I want them to show as

House Boat
Paper Plane
Green Grass
Red Baloon
Pink Shoes

in a single cell. Is there any way?

Thanks

 

[JoeMo]

Excel Workbook
	A	B
6	House Boat, Paper Plane, Green Grass, Red Baloon, Pink Shoes	House Boat Paper Plane Green Grass Red Baloon Pink Shoes
Sheet3

 

[sheetspread]

Replace line 13 with

Result = Result & " " & LookupRange.Cells(i, ColumnNumber) & chr(10)

 

[drshkd]

Thanks to all. Worked like a charm.